Are you talking about me again?



Christopher Bellizzi
SQL DBA/Systems Analyst
240 Fall Street
SFO Main Office
Seneca Falls, NY 13148
315-568-7886

-----Original Message-----
From: Jeremy Greaves
[mailto:mssqldba-ezmlmshield-x12153328.[Email address protected]
Sent: Tuesday, February 27, 2007 10:17 AM
To: LazyDBA Discussion
Subject: RE: Inner joining different servers

Or use OPENQUERY

SELECT * FROM OPENQUERY(<LinkedServerName>,
'SELECT
<Col1>,
<Col2>,
<Col3>
FROM <DBName>.<dbo_typically>.<TableName>
WHERE <Clause>'
)

Important things to note;
Your "second" SELECT statement is enclosed within single quotes.
If you use a literal string such as " WHERE <Col1> = 'Flibble'" make
sure
you double enclose your single quote, a la " WHERE <Col1> = ''Flibble''
"
and that is Two Single Quotes, not a Double Quote (somantics).
You use the Linked Server name as the first parameter of the OPENQUERY
clause.
Remember to close your parenthesis at the end of the OPENQUERY call.
DON'T use SELECT * for your enclosed SELECT, it's more polite, and
provides
SQL Server with a better idea of what it's getting, if you ask for
discrete
fields <COL1>,<Col2> etc has is less of an impact on performance.
The <dbo_typically> is the "schema" or "owner" identifier of the table
within the database; it's usually "dbo"...... but can be "bob",
"ApsDev",
etc etc....

This type of query passes the processing of the SELECT statement to the
Linked Server, and you just get the results back, rather than have your
PC/SQL Server do the "crunching".
Also within the second SELECT you could put the locking hint WITH
(NOLOCK)
after the table in the FROM part of the statement; FROM
<DBName>.<dbo>.<TABLE_Name> WITH (NOLOCK).
Then you're less likely to get that screaming DBA in your office.....
Ordinarily you can't do this as a suggested "hint" on a table via a
linked
server four part query type view, as SQL server will complain that you
can't
hint a remote table. However, the onus is on the remote server with this

type of query, 'cos effectively you've handed the SELECT statement to
the
Linked Server to deal with and you're waiting for the resultant record
set
I've not had the thing whine at me that it can't do it.

It's an alternative anyway...

Jeremy Greaves
Fenris Software Consulting, Inc




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


************************************
This e-mail and any files transmitted with it are proprietary and intended solely
for the use of the individual or entity to whom they are addressed. If you have
received this e-mail in error please notify the sender. Please note that any views
or opinions presented in this e-mail are solely those of the author and do not
necessarily represent those of ITT, Inc. The recipient should check
this e-mail and any attachments for the presence of viruses. ITT accepts
no liability for any damage caused by any virus transmitted by this e-mail.
************************************


MS Sql Server LazyDBA home page