Running queries on remote databases from SQL Server

0. Introduction

When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database.
Problems start when you try to combine that with some "user input" passed using parameters, usually in conjunction with stored procedures.

For simplifying I won't refer to a Stored Procedure in the below examples, instead use some script that could run "on it's own".

So, consider this rough setup:
I assume that the reader knows how to setup a Linked Server, otherwise you go to Creating Linked Servers

1. Usual techniques

There are several techniques to query the remote table, for example, to get the balance from the account 12345:

SELECT balance FROM LINKED_DB..TEST.ACCOUNT WHERE id = 12345

Select all Open in new window

or, with the parameter method:

declare @id int 
                      set @id = 12345
                      SELECT balance FROM LINKED_DB..TEST.ACCOUNT WHERE id = @id

Select all Open in new window

Syntax note: LINKED_DB..TEST.ACCOUNT is the so-called four-parts name, build as: <linked_server_name>.<database_name>.<schema_name>.<table_name>.
For some linked server drivers or providers, the database_name is not needed or even not supported, and the schema_name might not be needed either, so if the linked server is configured to connect with TEST login, you might even write:

SELECT balance FROM LINKED_DB...TEST_ACCOUNT

Select all Open in new window

A first issue with this could be that, for whatever reason, the filtering is not occurring on the remote db, but locally; which means that the full table data is fetched locally before filtering. Imagine you happen to get that on a big table!

And here is another issue you might run into, a error message like:
The OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_DB" supplied invalid metadata for column "LASTRESETDATE". The data type is not supported.

Huh? The column is not actually used in the query at all, still the complaint. I have no explanation yet, but as a workaround there are a couple of methods to examine:
Let's use the also well-known OPENQUERY syntax:

SELECT * FROM OPENQUERY(LINKED_DB, 'SELECT balance FROM TEST.ACCOUNT WHERE id = 12345')

Select all Open in new window

While this should work, and most will know, if the id value is a parameter, you would need to build the sql like this:

Declare @id int -- would be the parameter for the stored procedure, for example
                      set @id = 12345
                      
                      Declare @sql varchar(max)
                      Set @sql = 'SELECT * FROM OPENQUERY(LINKED_DB, ''SELECT balance FROM TEST.ACCOUNT WHERE id = ' + cast(@id as varchar(100)) + ' '') ' 
                      exec(@sql)

Select all Open in new window

That will execute fine, but you can surely see how dull this kind of code will look like, especially if the parameter is text and not a number (more quotes to be added …). Another danger here is that if the coder forgets to care about SQL Injection, the whole instance (and via the linked servers, even those remote databases) is at risk!
To get it even more interesting, you often want to actually retrieve some returned value(s), which would make it like this:

Declare @id int -- would be the parameter for the stored procedure, for example
                      set @id = 12345
                      Declare @balance int --- output value
                      
                      Declare @sql nvarchar(max)
                      Set @sql = 'SELECT @res = balance FROM OPENQUERY(LINKED_DB, ''SELECT balance FROM TEST.ACCOUNT WHERE id = ' + cast(@id as varchar(100)) + ' '') ' 
                      
                      exec sp_executesql @sql, N'@res int OUTPUT', @balance OUTPUT

Select all Open in new window

This also works to get several columns from the table, but still, the usuage of input parameter @id is not really "nice".

2. Nice way, available starting with MSSQL 2005

Here we go:

Declare @id int --- would be the parameter for the stored procedure, for example
                      set @id = 12345
                      
                      Declare @balance int --- output value
                      
                      Declare @sql nvarchar(max)
                      Set @sql = 'BEGIN SELECT balance INTO ? FROM TEST.ACCOUNT WHERE id = ?; END; ' 
                      
                      EXECUTE(@sql, @balance OUTPUT, @id) AT [LINKED_DB]

Select all Open in new window

Much easier to read, and no quoting problems, no data type issues.
Note that the order of the parameters to be passed has to match the order of the ? in the sql string, with the database as first!
The BEGIN/END part is specific to Oracle and the provider used, you might have to change that syntax a bit in your place.

It is still not perfect, but as close as you could come with queries on linked servers with parameters.

Of course, you can run plain sql to fetch multiple records, with parameters:EXECUTE ('SELECT * FROM TEST.ACCOUNT WHERE id >= ? AND ROWNUM <= ? ', 12345, 10) AT [LINKED_DB]
                      Or insert the results into a local (fixed, temp or variable) table:CREATE TABLE #tmp ( id INT , balance INT )
                      INSERT INTO #tmp 
                      EXECUTE ('SELECT id, balance FROM TEST.ACCOUNT WHERE id >= ? AND ROWNUM <= ? ', 12345, 10) AT [LINKED_DB]
                      Select * from #tmp
                      Drop table #tmp 

Select all Open in new window

3. Does it stop with SELECT?

Unlike the OPENQUERY method, you can perfectly run INSERT, UPDATE or DELETE using the EXECUTE ... AT method:

EXECUTE ('INSERT INTO TEST.ACCOUNT (id, balance) VALUES (?,?)', 54321, 0) AT [LINKED_DB]
                      EXECUTE ('UPDATE TEST.ACCOUNT SET balance = balance + ? WHERE id = ?', 10, 54321) AT [LINKED_DB]

Select all Open in new window

All you need to take care of is the order of the parameters!

4. Possible issues/errors

Besides the problems noted above, you could run into some other issues.

For example, when running queries with output parameters, you might receive this error message:

Msg 7411, Level 16, State 1, Line 1
                      Server 'LINKED_DB' is not configured for RPC.

Select all Open in new window

This error just tells you that you have to configure the RPC and RPC OUT in the linked server’s properties.

On execution of INSERT INTO <table> EXECUTE … you might run into this kind of errors:

OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_DB" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
                      Msg 7391, Level 16, State 2, Line 16
                      The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "LINKED_DB" was unable to begin a distributed transaction.

Select all Open in new window

This can be solved using the registry tweak described in general in this article: http://support.microsoft.com/kb/922430.
For LINKED_DB being Oracle, you need to include have
Oracle Services for Microsoft Transaction Server
installed on the SQL Server box as part of the Oracle Client installation.

5. Conclusion

As usual, any feedback is welcome, and I hope that this article was helpful to you!