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



MS Sql Server LazyDBA home page