The second way to query a linked server is to use the OPENQUERY command. This command is perfect for heterogeneous databases because it executes the requested query on the remote system, not on the SQL Server system that hosts the linked server. I've had many queries that wouldn't work with the four-part identifier but work fine with the OPENQUERY command. When you use the OPENQUERY command, you instruct SQL Server to select all records that the query specifies between the quotation marks:
SELECT * FROM OPENQUERY(LINKEDSERVER, "SELECT * FROM northwind..Categories where CategoryName Like 'Sea%'")
The first required piece of syntax is the linked server name, followed by the query. Notice that you need to use single quotes around the conditional piece of the query.
The third way to query a remote provider is to use the OPENROWSET command, which doesn't require that you have a linked server set up beforehand. This command uses linked server technology but creates the link at runtime, letting you dynamically set up the server you want to connect to. The OPENROWSET command operates the same as the OPENQUERY command: It executes all queries on the remote server. The example query in Listing 1 uses the OPENROWSET command. Listing 1's example code also shows that you can use the OPENROWSET (or OPENQUERY) command to perform joins.
Although OPENROWSET gives you additional flexibility and power to dynamically create connections, I don't recommend using this command unless you can absolutely justify a need. Having a centralized location where you can configure your linked server is much more convenient than having to recompile your code every time you make a connection change.
Gathering Meta Data
The stored procedure I use most for gathering linked server data is sp_linkedservers. This stored procedure tells you how many linked servers are configured and gives you information about them. The procedure is handy if you have obscure names for your linked servers because remembering such names can be difficult. If you haven't established a linked server, you'll see only one result: the local server's information.
You can also request a list of databases that are on your linked servers by using the sp_catalogs stored procedure. This stored procedure uses the @server_name variable, which is the name of the linked server you're querying:
sp_catalogs @server_name = 'LINKEDSERVER'
You can request a list of tables in a database by using the sp_tables_ex stored procedure:
EXEC sp_tables_ex @table_server = 'LINKEDSERVER', @table_catalog='northwind', @table_schema='dbo', @table_name='Suppliers'
Using some of the nonrequired parameters, such as @table_catalog and @table_name, is important when you want a list of only a subset of the database's tables. If you don't use the @table_name parameter, for example, the stored procedure will return every table in the database. If you don't use the @table_catalog parameter, the stored procedure will return the default database's tables only.
You can use several other stored procedures to obtain meta data about your linked server's tables and indexes. For a complete list of other stored procedures, see SQL Server Books Online (BOL).
Many companies can't or don't plan to upgrade their non-SQL Server infrastructures to SQL Server either because of the cost or because of the complexity. In these cases, linked servers let you build applications that take advantage of SQL Server's features but still access your legacy data. Linked servers also let you partition your data geographically or logically, dividing the load among the various servers.