Can user view objects they only have select permission on?

I have a user that belongs to a role. This role only has select permissions
on 10 views. When I log in as this user via Management Studio I cannot see
the views however I can execute queries against them.
On another server that I did not setup, that I'm supposed to be mimicking
the same security, this same user can see the views.
Any ideas what the difference is?
Thanks!Here's the commands that I'm executing in order:
CREATE ROLE [Customers_ROLE] Authorization dbo
CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] Deny
View
Definition to [Customers_Role]
exec sp_adduser 'phenson', 'phenson', [Customers_Role]
GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
When I log in as phenson I do not see PT_View but I can query on it. I need
to be able to see it.
"SpankyATL" wrote:

> I have a user that belongs to a role. This role only has select permissio
ns
> on 10 views. When I log in as this user via Management Studio I cannot se
e
> the views however I can execute queries against them.
> On another server that I did not setup, that I'm supposed to be mimicking
> the same security, this same user can see the views.
> Any ideas what the difference is?
> Thanks!|||I thought I'd answer my own question for those of you who come across this
some day. I need to remove the "Deny View Definition" portion and that took
care of it. The user was able to see that view and execute it but could not
see the script.
"SpankyATL" wrote:
[vbcol=seagreen]
> Here's the commands that I'm executing in order:
> CREATE ROLE [Customers_ROLE] Authorization dbo
> CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] De
ny View
> Definition to [Customers_Role]
> exec sp_adduser 'phenson', 'phenson', [Customers_Role]
> GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
>
> When I log in as phenson I do not see PT_View but I can query on it. I ne
ed
> to be able to see it.
> "SpankyATL" wrote:
>|||SpankyATL (SpankyATL@.discussions.microsoft.com) writes:
> Here's the commands that I'm executing in order:
> CREATE ROLE [Customers_ROLE] Authorization dbo
> CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] De
ny View
> Definition to [Customers_Role]
> exec sp_adduser 'phenson', 'phenson', [Customers_Role]
> GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
>
> When I log in as phenson I do not see PT_View but I can query on it. I
> need to be able to see it.
Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
member of?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I was working from a script that was given to me. The person who developed
it mistakenly thought deny view would only deny the user from viewing the
source code.
"Erland Sommarskog" wrote:

> SpankyATL (SpankyATL@.discussions.microsoft.com) writes:
> Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
> member of?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Can SQLPutData be used against varchar(max)?

Hi,
I am trying to insert data into varchar(max) via ODBC.
When I try to insert data. I am getting following error. Is SQLPutData
supported for varchar(max)?
1394-1e4c ENTER SQLPutData
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)
DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The text, ntext,
or image pointer value conflicts with the column name specified. (7125)
Thank you,
KM
I realized that I was binding the parameter with SQL_LONGVARCHAR.
It worked when I used SQL_VARCHAR.
Can't we use SQL_LONGVARCHAR for binding varchar(max)? Aren't they
completely compatible?
Thank you,
KM
"KM" wrote:

> Hi,
> I am trying to insert data into varchar(max) via ODBC.
> When I try to insert data. I am getting following error. Is SQLPutData
> supported for varchar(max)?
> 1394-1e4c ENTER SQLPutData
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> 1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
> insert/update. The insert/update of a text or image column(s) did not
> succeed. (0)
> DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The text, ntext,
> or image pointer value conflicts with the column name specified. (7125)
> Thank you,
> KM

Can SQLPutData be used against varchar(max)?

Hi,
I am trying to insert data into varchar(max) via ODBC.
When I try to insert data. I am getting following error. Is SQLPutData
supported for varchar(max)?
1394-1e4c ENTER SQLPutData
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)
DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The t
ext, ntext,
or image pointer value conflicts with the column name specified. (7125)
Thank you,
KMI realized that I was binding the parameter with SQL_LONGVARCHAR.
It worked when I used SQL_VARCHAR.
Can't we use SQL_LONGVARCHAR for binding varchar(max)? Aren't they
completely compatible?
Thank you,
KM
"KM" wrote:

> Hi,
> I am trying to insert data into varchar(max) via ODBC.
> When I try to insert data. I am getting following error. Is SQLPutData
> supported for varchar(max)?
> 1394-1e4c ENTER SQLPutData
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> 1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
> insert/update. The insert/update of a text or image column(s) did not
> succeed. (0)
> DIAG [42000] [Microsoft][SQL Native Client][SQL Server]
The text, ntext,
> or image pointer value conflicts with the column name specified. (7125)
> Thank you,
> KM

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:

>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to sto
p
>the SQL agent.

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.
Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:

>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to stop
>the SQL agent.

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:
>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to stop
>the SQL agent.

Can SQL Server 2005 using C# make a SOAP call do set operations?

We have two remote data sources that we will access via a SOAP call.
But then we need to operate on the two sets (remove dups, etc.) and,
ideally, return a SQL result set to the client app.
So questions:
* Can we make a SOAP call using C# from within SQL Server?
* Can SQL Server operate on the resulting data (an XML record)?
* Can SQL Server return the combined results as a standard results set?
Thank youHello shp.jc,

> * Can we make a SOAP call using C# from within SQL Server?
Yes.

> * Can SQL Server operate on the resulting data (an XML record)?
Yes, but you'll probably want to do that with CLR code.

> * Can SQL Server return the combined results as a standard results
> set?
Yes, but recommend loading the data from the SOAP into a temp table and then
doing that work with T-SQL. Note that you can just have the Spoc or Function
turn an XML instance and query over that with Xquery.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Can sp_changearticle help when needing to change the primary key?

We need to add a new column to a table that is part of a multi-table
publication replicated via transactional replication. We've read (and
re-read) the sp_changearticle page in Books Online without fully
understanding how we might be able to use this command to help with our task.
Can someone please provide two important answers -- 1) can we use
sp_changearticle to make this kind of change to our publication? 2) can you
offer an example of how sp_changearticle is coded for such purposes.
Actually, we'd be interested in seeing how sp_changearticle is coded in
general, even if it cannot be used for our particular task.
Thanks,
Barry Spiegel
barry.spiegel@.eds.com
2) sp_changearticle 'pubs', 'jobs','description','this is the new
description'
1) no, you use use sp_repladdcolumn like this:
sp_repladdcolumn 'jobs','intcol','int not null default(1)'
This column will be modified in all publications and their subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Barry Spiegel" <Barry Spiegel@.discussions.microsoft.com> wrote in message
news:732D9DB5-C5A6-4A65-AEB7-37384C791401@.microsoft.com...
> We need to add a new column to a table that is part of a multi-table
> publication replicated via transactional replication. We've read (and
> re-read) the sp_changearticle page in Books Online without fully
> understanding how we might be able to use this command to help with our
task.
> Can someone please provide two important answers -- 1) can we use
> sp_changearticle to make this kind of change to our publication? 2) can
you
> offer an example of how sp_changearticle is coded for such purposes.
> Actually, we'd be interested in seeing how sp_changearticle is coded in
> general, even if it cannot be used for our particular task.
> Thanks,
> Barry Spiegel
> barry.spiegel@.eds.com
>

Can someone help me with multiple "Left Outer Joins"?

I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.

Using three "Left Outer Joins" slows the system down considerably.

I've tried creating a temp db, but I can't figure out how to execute
two select commands. (It throws the exception "The column prefix
'tempdb' does not match with a table name or alias name used in the
query.")

Looking for suggestions (and a lesson or two!) This is my first attempt
at SQL.

Current (working, albeit slowly) Query Below

TIA

SELECT

LEDGER_ENTRY.entry_amount,
LEDGER_TRANSACTION.credit_card_exp_date,
LEDGER_ENTRY.entry_datetime,
LEDGER_ENTRY.employee_id,
LEDGER_ENTRY.voucher_explanation,
LEDGER_ENTRY.card_reader_used_ind,
STAY.room_id,
GUEST.guest_lastname,
GUEST.guest_firstname,
STAY.arrival_time,
STAY.departure_time,
STAY.arrival_date,
STAY.original_departure_date,
STAY.no_show_status,
STAY.cancellation_date,
FOLIO.house_acct_id,
FOLIO.group_code,
LEDGER_TRANSACTION.original_receipt_id

FROM

mydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,
mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_FOLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.folio_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay_id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

WHERE

LEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_id
AND FOLIO.folio_id = LEDGER_TRANSACTION.folio_id
AND LEDGER_ENTRY.payment_method='3737******6100'
AND LEDGER_ENTRY.property_id='abc123'

ORDER BY

LEDGER_ENTRY.entry_datetime DESCWhat is actually your question :-) ?

Jens Suessmeyer.|||My question is, Can this query be further optimized for speed?

I have tried creating temporary databases, to break-up the outer joins
into different select commands, but I couldn't get it to work properly.
I'm using VB6 and ADO, invoking the execute method of the adodb.command
object to return the recordset.|||To take access to different databases and tables you may
use for example syntax like this:
DatabaseName.TableName.ColumnName

I do not see why you would need to create a
temporal db and why this would help you
with performance.

I wonder if you meant a temporal table instead.

In general I experienced, that views (depending on what the do) may slow
down the whole query.
also ORDER BY.

I suggest you break your select statement in three peaces so you may
see with the profiler wich join would take the most of time.
maybe by applying an index to specific columns you get a bit more
performance.

if you watch the query from your VB-application, you wil have to
differ between the time thats used by your application and ADO
and the time the Database itself needs.
the bottleneck could also be at the application-side!

Hope this gave some hints.

Sonja

"Steve" <budgethelp@.yahoo.com> schrieb im Newsbeitrag
news:1126754368.398119.129660@.o13g2000cwo.googlegr oups.com...
>I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
> "Left Outer Joins" in order to return every transaction for a specific
> set of criteria.
> Using three "Left Outer Joins" slows the system down considerably.
> I've tried creating a temp db, but I can't figure out how to execute
> two select commands. (It throws the exception "The column prefix
> 'tempdb' does not match with a table name or alias name used in the
> query.")
> Looking for suggestions (and a lesson or two!) This is my first attempt
> at SQL.
> Current (working, albeit slowly) Query Below
> TIA
> SELECT
> LEDGER_ENTRY.entry_amount,
> LEDGER_TRANSACTION.credit_card_exp_date,
> LEDGER_ENTRY.entry_datetime,
> LEDGER_ENTRY.employee_id,
> LEDGER_ENTRY.voucher_explanation,
> LEDGER_ENTRY.card_reader_used_ind,
> STAY.room_id,
> GUEST.guest_lastname,
> GUEST.guest_firstname,
> STAY.arrival_time,
> STAY.departure_time,
> STAY.arrival_date,
> STAY.original_departure_date,
> STAY.no_show_status,
> STAY.cancellation_date,
> FOLIO.house_acct_id,
> FOLIO.group_code,
> LEDGER_TRANSACTION.original_receipt_id
> FROM
> mydb.dbo.LEDGER_ENTRY LEDGER_ENTRY,
> mydb.dbo.LEDGER_TRANSACTION LEDGER_TRANSACTION,
> mydb.dbo.FOLIO FOLIO
> LEFT OUTER JOIN
> mydb.dbo.STAY_FOLIO STAY_FOLIO
> ON
> FOLIO.folio_id = STAY_FOLIO.folio_id
> LEFT OUTER JOIN
> mydb.dbo.STAY STAY
> ON
> STAY_FOLIO.stay_id = STAY.stay_id
> LEFT OUTER JOIN
> mydb.dbo.GUEST GUEST
> ON
> FOLIO.guest_id = GUEST.guest_id
> WHERE
> LEDGER_ENTRY.trans_id = LEDGER_TRANSACTION.trans_id
> AND FOLIO.folio_id = LEDGER_TRANSACTION.folio_id
> AND LEDGER_ENTRY.payment_method='3737******6100'
> AND LEDGER_ENTRY.property_id='abc123'
> ORDER BY
> LEDGER_ENTRY.entry_datetime DESC|||Yes, I meant that I tried to create a temporary table, not db, sorry...

Regarding the 3 joins...would putting parenthesis around any of them
help?
How are they being processed exactly?
The first join has a single table reference immediately preceding the
join statement, but the others cannot (is that correct?)
What are the next two joins being joined to exacty (since there is no
table specified before the two join statements?

The tables that I'm joining look like this:

--FOLIO-----STAY FOLIO------STAY
|
|
|___________GUEST

All transactions have FOLIO records, but not all transactions have STAY
FOLIO, STAY, OR GUEST records.
I need to return all transactions that have a folio record.

This is the syntax I'm using to accomplish this:

mydb.dbo.FOLIO FOLIO
LEFT OUTER JOIN
mydb.dbo.STAY_FOLIO STAY_FOLIO
ON
FOLIO.folio_id = STAY_FOLIO.folio_id
LEFT OUTER JOIN
mydb.dbo.STAY STAY
ON
STAY_FOLIO.stay_id = STAY.stay_id
LEFT OUTER JOIN
mydb.dbo.GUEST GUEST
ON
FOLIO.guest_id = GUEST.guest_id

I don't understand how the order of the joins affects their processing.
Is there a better way to phrase the joins, given the table
relationships as outlined above?

Thanks!|||> Yes, I meant that I tried to create a temporary table, not db, sorry...

this would be accomplished with views. Like I mentioned before
but this may not be a Solution for your problem.

As I know a lot of Select Squences with a lot more Joins
than you need here, I do not believe that your performance-problem
results from the sql statement.
Depending on the server-machine your Database is installed on,
there may be different reasons, why this query takes a long time.

1)Maybe your tables are big. Lets asume each of them has 1 000 000 tuples.
Even then the query should not last (DEPENDING ON YOUR MACHINE)
a "long" time.
If this Machine is for example the whole time working on a 70% level
it slows down everything to death.
If the machine has enough breath to acomplish your query and you are testing
just solely we leave this section ...

2)the dbms tries to optimize sql -queries by itself, to make them faster, if
you want to
optimize more, use only the lines and columns you seek. It makes the whole
thing
a little faster if you simply snip columns and rows that you do not need.

3) it may help with performance to apply indexes to columns that will be
joined

4) Your application is getting all data over network one by one and
everything
slows down. Then its not a database or query -problem

5) use the SQL Profiler to see where the bottleneck is.

If you like, create for each join a view and then simply join the view with
folio
like this for example
------------
CREATE VIEW stay_test AS
Select Stay_folio.folio_id from
STAY_FOLIO left outer join STAY
ON
stay_folio.stay_id = stay.stay_id
-----------
SELECT * FROM
folio LEFT OUTER JOIN stay_test
ON
folio.folio_id = stay_test.folio_id
LEFT OUTER JOIN guest
ON
folio.guest_id = guest.guest_id
-----------

At the SQL profiler you can view each selection that is made and how long it
takes to get result

> Regarding the 3 joins...would putting parenthesis around any of them
> help?
> How are they being processed exactly?
> The first join has a single table reference immediately preceding the
> join statement, but the others cannot (is that correct?)
> What are the next two joins being joined to exacty (since there is no
> table specified before the two join statements?
> The tables that I'm joining look like this:
> --FOLIO-----STAY FOLIO------STAY
> |
> |
> |___________GUEST
> All transactions have FOLIO records, but not all transactions have STAY
> FOLIO, STAY, OR GUEST records.
> I need to return all transactions that have a folio record.
> This is the syntax I'm using to accomplish this:
> mydb.dbo.FOLIO FOLIO
> LEFT OUTER JOIN
> mydb.dbo.STAY_FOLIO STAY_FOLIO
> ON
> FOLIO.folio_id = STAY_FOLIO.folio_id
> LEFT OUTER JOIN
> mydb.dbo.STAY STAY
> ON
> STAY_FOLIO.stay_id = STAY.stay_id
> LEFT OUTER JOIN
> mydb.dbo.GUEST GUEST
> ON
> FOLIO.guest_id = GUEST.guest_id
> I don't understand how the order of the joins affects their processing.
> Is there a better way to phrase the joins, given the table
> relationships as outlined above?
> Thanks!|||On 14 Sep 2005 20:19:28 -0700, Steve wrote:

>I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
>"Left Outer Joins" in order to return every transaction for a specific
>set of criteria.
>Using three "Left Outer Joins" slows the system down considerably.

Hi Steve,

That need not be the case. I guess that adding the right indexes would
help a lot.

>I've tried creating a temp db, but I can't figure out how to execute
>two select commands. (It throws the exception "The column prefix
>'tempdb' does not match with a table name or alias name used in the
>query.")

I could help you with solving this problem, but I won't. Breaking a
query in smaller pieces with temp tables has a fair chance to hurt your
performance, and very limited chance to do any good.

The query optimizer can use all the tricks that you can use, and then
some. Better to trust that the optimizer will pick the right execution
plan from the flock of available options instead of forcing it to do the
way you think is best. There ARE cases where the optimizer does need
some guidance, but they are the exception rather than the rule.

>Current (working, albeit slowly) Query Below

Thanks for posting the query, but you'll have to provide a lot more
information to enable us to help you. We need to know the structure of
your tables (posted as CREATE TABLE statements, including all properties
and constraints, but excluding irrelevant columns), the indexes you have
defined for your tables, if any (posted as CREATE INDEX statements), a
few rows of sample data (posted as INSERT statements) and the expected
results from that sample data to give us an idea what you're trying to
achieve. Including a short description of your actual business problem
is a great idea too. See www.aspfaq.com/5006 for some useful pointers on
hjow to assemble the information we need, in the best format.

Oh, and we'd also like to know how many rows (approximately) you have in
each of your tables - and the execution plan that is currently used for
your query (you can get the execution plan if you run the query with SET
SHOWPLAN_ALL ON.

With that information, we can try to find out why your current query is
running slow, and how to remedy that.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Steve (budgethelp@.yahoo.com) writes:
> I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
> "Left Outer Joins" in order to return every transaction for a specific
> set of criteria.
> Using three "Left Outer Joins" slows the system down considerably.
> I've tried creating a temp db, but I can't figure out how to execute
> two select commands. (It throws the exception "The column prefix
> 'tempdb' does not match with a table name or alias name used in the
> query.")
> Looking for suggestions (and a lesson or two!) This is my first attempt
> at SQL.

As Hugo pointed out, it is impossible to give very precise advice from
from the information you have posted. Assuming that there is an index
on (payment_method, property_id) on LEDGER_ENTRY, and that all other
tables have indexes on the columns you join on, I would expect the query
to perform well. Then again, there can be several reasons to why it does
not.

I analysed your query, and I think that I found one flaw. Here is a
rewritten version:

SELECT LE.entry_amount, LT.credit_card_exp_date, LE.entry_datetime,
LE.employee_id, LE.voucher_explanation, LE.card_reader_used_ind,
S.room_id, G.guest_lastname, G.guest_firstname, S.arrival_time,
S.departure_time, S.arrival_date, S.original_departure_date,
S.no_show_status, S.cancellation_date, F.house_acct_id,
F.group_code, LT.original_receipt_id
FROM mydb.dbo.LEDGER_ENTRY LE
JOIN mydb.dbo.LEDGER_TRANSACTON LT ON LE.trans_id = LT.trans_id
JOIN mydb.dbo.FOLIO F ON F.folio_id = LT.folio_id
LEFT JOIN (mydb.dbo.STAY_FOLIO SF
JOIN mydb.dbo.STAY S ON SF.stay_id = S.stay_id)
ON F.folio_id = SF.folio_id
LEFT JOIN mydb.dbo.GUEST G ON F.guest_id = G.guest_id
WHERE LE.payment_method='3737******6100'
AND LE.property_id='abc123'
ORDER BY LE.entry_datetime DESC

This alters the semantics of the query slightly, and I guess to the
good. Whether it affects performance, I don't know.

One potential problem is if the joins from FOLIO to STAY_FOLIO and GUEST
could hit multiple rows in the latter tables. In such case you get too
many rows back, which also could cause poor performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can only see system tables

Hello,

I have created a linked server XYZ that is linked to server ABC. I am tying to view the tables via XYZ but I'm unable to do so. I can only see the system tables. When I run a select statement, I get the correct results. That means I have the access to the tables, yet why am I not able to see the tables.

Please assist

That highly depends on the type of linked server you are accessing. Which server are you accessing ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Can not start tcp port 1433 on MSDE

I've got an installation of MSDE that insists on only allowing shared memory
access. This works fine but I would like to access th db via tcp. I have
ran SVRNETCN.exe and verified that TCP is selected. I have even verified
that the port number etc. is set in the registry. It just refuses to
avtivate tcp when I restart MSDE or the box it's runinning on.
Here's the errorlog:
2004-04-30 00:03:33.20 server Microsoft SQL Server 2000 - 8.00.884
(Intel X86)
Nov 29 2003 20:52:47
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine (Windows) on Windows NT 5.2 (Build 3790: )
2004-04-30 00:03:33.20 server Copyright (C) 1988-2002 Microsoft
Corporation.
2004-04-30 00:03:33.20 server All rights reserved.
2004-04-30 00:03:33.20 server Server Process ID is 4416.
2004-04-30 00:03:33.20 server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\LOG\ERRORLOG'.
2004-04-30 00:03:33.30 server SQL Server is starting at priority class
'normal'(1 CPU detected).
2004-04-30 00:03:33.38 server SQL Server configured for thread mode
processing.
2004-04-30 00:03:33.38 server Using dynamic lock allocation. [500] Lock
Blocks, [1000] Lock Owner Blocks.
2004-04-30 00:03:33.40 spid3 Starting up database 'master'.
2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
2004-04-30 00:03:34.21 server SQL Server is ready for client connections
2004-04-30 00:03:34.21 spid5 Starting up database 'model'.
2004-04-30 00:03:34.27 spid3 Server name is 'CHEF\SHAREPOINT'.
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 4
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 5
2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 6
2004-04-30 00:03:34.44 spid5 Clearing tempdb database.
2004-04-30 00:03:35.35 spid5 Starting up database 'tempdb'.
2004-04-30 00:03:35.55 spid3 Recovery complete.
Where in that log do you see evidence of SQL Server "refusing to activate
tcp"? Did you really mean, "I can't connect to SQL Server on port 1433"?
If so, I would check your network/router/firewall settings before assuming
the problem is service- or machine-specific.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Karl Foley" <karl@.thefoleyhouse.co.uk> wrote in message
news:e3R9sIkLEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I've got an installation of MSDE that insists on only allowing shared
> memory
> access. This works fine but I would like to access th db via tcp. I have
> ran SVRNETCN.exe and verified that TCP is selected. I have even verified
> that the port number etc. is set in the registry. It just refuses to
> avtivate tcp when I restart MSDE or the box it's runinning on.
> Here's the errorlog:
> 2004-04-30 00:03:33.20 server Microsoft SQL Server 2000 - 8.00.884
> (Intel X86)
> Nov 29 2003 20:52:47
> Copyright (c) 1988-2003 Microsoft Corporation
> Desktop Engine (Windows) on Windows NT 5.2 (Build 3790: )
> 2004-04-30 00:03:33.20 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2004-04-30 00:03:33.20 server All rights reserved.
> 2004-04-30 00:03:33.20 server Server Process ID is 4416.
> 2004-04-30 00:03:33.20 server Logging SQL Server messages in file
> 'C:\Program Files\Microsoft SQL Server\MSSQL$SHAREPOINT\LOG\ERRORLOG'.
> 2004-04-30 00:03:33.30 server SQL Server is starting at priority class
> 'normal'(1 CPU detected).
> 2004-04-30 00:03:33.38 server SQL Server configured for thread mode
> processing.
> 2004-04-30 00:03:33.38 server Using dynamic lock allocation. [500] Lock
> Blocks, [1000] Lock Owner Blocks.
> 2004-04-30 00:03:33.40 spid3 Starting up database 'master'.
> 2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
> 2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
> 2004-04-30 00:03:34.21 server SQL Server is ready for client
> connections
> 2004-04-30 00:03:34.21 spid5 Starting up database 'model'.
> 2004-04-30 00:03:34.27 spid3 Server name is 'CHEF\SHAREPOINT'.
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 4
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 5
> 2004-04-30 00:03:34.27 spid3 Skipping startup of clean database id 6
> 2004-04-30 00:03:34.44 spid5 Clearing tempdb database.
> 2004-04-30 00:03:35.35 spid5 Starting up database 'tempdb'.
> 2004-04-30 00:03:35.55 spid3 Recovery complete.
>
>
|||"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%237IBIxnLEHA.2592@.tk2msftngp13.phx.gbl...
> Where in that log do you see evidence of SQL Server "refusing to activate
> tcp"?
Thanks for the offer of help, I'm just abut ready to give up on this!
Just about here:
2004-04-30 00:03:34.20 server Using 'SSNETLIB.DLL' version '8.0.880'.
2004-04-30 00:03:34.21 server SQL server listening on Shared Memory.
2004-04-30 00:03:34.21 server SQL Server is ready for client connections
You can see it's only using shared memory.

> Did you really mean, "I can't connect to SQL Server on port 1433"?
> If so, I would check your network/router/firewall settings before assuming
> the problem is service- or machine-specific.
No. I've tried to read all the posts on here regarding the problem and have
done multiple searches on google to try and solve this problem. No matter
what I do, MSDE will not listen on TCP port 1433. Here's a quick snip of
the output of netstat -an:
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:25 0.0.0.0:0 LISTENING
TCP 0.0.0.0:53 0.0.0.0:0 LISTENING
[Snip]
TCP 0.0.0.0:691 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1028 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1533 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1723 0.0.0.0:0 LISTENING
[Snip]
As you can see, no port 1433 is listening here. I have only tried
connecting to port 1433 from this machine but can't see the point of trying
another machine if I can see the port is not open. This machine does not
have any firewall software enabled\installed on it. That functionality is
being provided by a hardware device.
As the version of MSDE seems so much newer than anything else I've seen
posted I am beginning to wonder if it is some kind of security feature...
|||> As the version of MSDE seems so much newer
Yes, 880 is quite a recent patch. Care to share where you obtained it?
Anyway, run svrnetcn.exe (it should execute from command line just like
that, otherwise it's in SQL Server's Tools\Binn folder) and make sure TCP/IP
is on the enabled side. If it isn't, move it over, hit apply, and restart
SQL Server. If that doesn't help, highlight TCP/IP and hit properties.
Make sure hide server is unchecked, then try changing the default TCP port
to some other number, hit apply, restart SQL Server, then change the default
TCP port back to 1433, hit apply, and restart SQL Server.
If TCP/IP is the ONLY enabled protocol, you might try enabling named pipes
just for fun, and then see http://support.microsoft.com/?id=306865 for a
registry setting that may need a fix.
If this is the way it has worked since you installed MSDE, you might
consider uninstalling and reinstalling (from SP3a) making sure to set
DISABLENETWORKPROTOCOL=0 in the setup ini file. Your patch level of 880
leads me to believe that installing from scratch would be the cleanest
solution anyway... sometimes these one-off patches have various side effects
that simply haven't been tested (which is why they are typically not
announced and readily available to the masses).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||You might also need to check to see if it is truly MSDE or is it WMSDE.. If
it is WMSDE it can only be accessed from the same machine. Based on the
instance name of SharePoint I would assume that it is really WMSDE..
http://www.microsoft.com/resources/d...us/stsf17.mspx
If you need to access/use it for other purposes and/or from other machines
you will need to upgrade the instance to full SQL server.. or another
possible option would be to use a different instance of MSDE for your other
needs.

Hope that helps,
David Copeland
Microsoft Small Business Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:u0HZpsyLEHA.2148@.TK2MSFTNGP09.phx.gbl...
> Yes, 880 is quite a recent patch. Care to share where you obtained it?
> Anyway, run svrnetcn.exe (it should execute from command line just like
> that, otherwise it's in SQL Server's Tools\Binn folder) and make sure
TCP/IP
> is on the enabled side. If it isn't, move it over, hit apply, and restart
> SQL Server. If that doesn't help, highlight TCP/IP and hit properties.
> Make sure hide server is unchecked, then try changing the default TCP port
> to some other number, hit apply, restart SQL Server, then change the
default
> TCP port back to 1433, hit apply, and restart SQL Server.
> If TCP/IP is the ONLY enabled protocol, you might try enabling named pipes
> just for fun, and then see http://support.microsoft.com/?id=306865 for a
> registry setting that may need a fix.
> If this is the way it has worked since you installed MSDE, you might
> consider uninstalling and reinstalling (from SP3a) making sure to set
> DISABLENETWORKPROTOCOL=0 in the setup ini file. Your patch level of 880
> leads me to believe that installing from scratch would be the cleanest
> solution anyway... sometimes these one-off patches have various side
effects
> that simply haven't been tested (which is why they are typically not
> announced and readily available to the masses).
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
|||"David Copeland [MSFT]" <davidcop@.online.microsoft.com> wrote in message
news:e7Aoio6LEHA.3216@.TK2MSFTNGP12.phx.gbl...
> You might also need to check to see if it is truly MSDE or is it WMSDE..
If
> it is WMSDE it can only be accessed from the same machine. Based on the
> instance name of SharePoint I would assume that it is really WMSDE..
Fantastic - That's exactly it.
I installed another instance of MSDE and this runs perfectly over TCP port
1433 as expected. I found it confusing because:
I didn't know that WMSDE was a different product to MSDE or that WMSDE
indeed even existed.
The tools are all there to enable network connectivity even though WMSDE
doesn't use them.
Thanks everyone.