How to use EXEC() at Linked Server

A special feature added in SQL 2005 is that you can use EXEC() to run pass-through queries on a linked server.

This could be another instance of SQL Server, but it could also be an Oracle server, an Access database, Active directory or whatever. The SQL could be a single query or a sequence of statements, and could it be composed dynamically or be entirely static. The syntax is simple, as seen by this example:

EXEC('SELECT COUNT(*) FROM ' + @db + '.dbo.sysobjects') AT SQL2K

SQL2K is here a linked server that has been defined with sp_addlinkedserver.

There is one thing that you can do with EXEC() at a linked server, that you cannot do with EXEC() on a local server: you can use parameters, both for input and output. The confuse matters, you don't use parameters with names starting with @, instead you use question marks (?) as parameter holders. Say that you are on an SQL 2005 box, and you are dying to know how many orders VINET had in the Northwind database. Unfortunately, SQL 2005 does not ship with Northwind, but you have a linked server set up to an instance of SQL 2000 with Northwind.

You can run this:

DECLARE @cnt int
EXEC('SELECT ? = COUNT(*) FROM Northwind.dbo.Orders WHERE CustomerID = ?',
@cnt OUTPUT, N'VINET') AT SQL2K
SELECT @cnt

Note here that the parameter values must appear in the order the parameter markers appear in the query. When passing a parameter, you can either specify a constant value or a variable.

You may ask why the inconsistency with a different parameter marker from sp_executesql? Recall that linked servers in SQL Server are always accessed through an OLE DB provider, and OLE DB uses ? as the parameter marker, a convention inherited from ODBC. OLE DB translates that parameter marker as is appropriate for the data source on the other end. (Not all RDBMS use @ for variables.)

As with regular EXEC(), you can specify AS USER/LOGIN to use impersonation:

EXEC('SELECT COUNT(*) FROM ' + @db + '.dbo.sysobjects')
AS USER = 'davidson' AT SQL2K

The login to use on the remote server can be defined with sp_addlinkedsrvlogin.