Executing Queries on Remote Servers

I have two servers, and I want to call a stored procedure with the server name as the parameter and have the stored procedure's T-SQL query run against the appropriate server, as in the following code:

CREATE PROCEDURE (@prmServer varchar(500)) ASSELECT * FROM  ##.pubs.dbo.TitlesGO

How do I execute the procedure's T-SQL query on the correct server by using the server parameter (the ## part of the object name) and without converting the T-SQL code to a string or using an EXECUTE statement? Does SQL Server provide an object for server name, such as ObjectReference in the statement

SELECT * FROM  ObjectReference(@prmServer)  .pubs.dbo.Titles

that I can use in place of the server's name? Or can I map @prmServer to an alias such as TempServer, as in

SELECT * FROM  TempServer.pubs.dbo.Titles

then run my T-SQL code?

Many database applications use object aliases to provide code portability or multiple data-source selectivity when referencing objects with three- or four-part names, thus avoiding programmers having to hard-code which server or database object to use. Depending on why you want to use aliases, you have a couple of options.

You might need to support release management, for example, in which you want your code to be server independent and run against a single remote server, which you pass in at runtime. In this case, I recommend using server-side network aliases. The cliconfig.exe tool lets you map an alias to a server; just set up one alias to point to whichever server you want to access. For example, on TestServer, you'd point the alias RemoteServer to TestRemoteServer, but on ProdServer, you'd point the alias RemoteServer to ProdRemoteServer. With this implementation, you can release your code into production without change as long as the linked server definition uses the RemoteServer alias. (Note that SQL Server 2005 implements database object synonyms, so you can run an install-time script to set up synonyms for the remote objects and include the right server name in the synonym.)

The second scenario is dynamic remote server access, in which your code needs to access any of several remote servers, depending on the user's runtime choices. You might need dynamic remote server access for an application that lets the user pick from a live database or an archive database, for example. In this case, you likely need to use dynamic query execution to run the T-SQL query on the appropriate server. Or, consider using the OPENQUERY() or OPENROWSET() functions, which let you dynamically select remote database objects. And unlike linked servers, OPENQUERY() and OPENROWSET() let you use remote query hints such as NOLOCK.

Alternatively, if you have just a few objects (for example, only a live and an archive database), you can simply use IF..ELSE logic to select the execution path at runtime, as the following example shows:

IF @server = N'Live"  SELECT  FROM productionserver.productiondatabase.dbo.datatableELSE  SELECT  FROM archiveserver.archivedatabase.dbo.datatable

—Patrick Conlan
Platform Program Manager
Microsoft Project Team

Learn more from "Executing Stored Procedures Remotely" and "Integrating with a Remote Database and Indexes."