Running queries on remote databases from SQL Server: linked server parameter dynamic sql

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:

A SQL 2005+ instance/database named SQL_DB

Some (remote) Oracle (or whatever type) database named REMOTE_DB

A linked server configured on the MS SQL 2005+ instance to that remote db, named LINKED_DB

On that (remote) database, a table named TEST.ACCOUNT, with a couple of colums with all possible data types

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

1:

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

1:
2:
3:

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

1:

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:

You can create a VIEW on the REMOTE_DB, that returns all the problematic columns with mapped data types. This might not be possible in all cases, or result in other problems.

You could try to change to another Provider/Driver to find the one that works for this query, but eventually will break other things elsewhere.

Try some other query technique from below.

Let's use the also well-known OPENQUERY syntax:

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

1:

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)

1:
2:
3:
4:
5:
6:

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

1:
2:
3:
4:
5:
6:
7:
8:

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]

1:
2:
3:
4:
5:
6:
7:
8:
9:

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

1:
2:
3:
4:
5:
6:

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]

1:
2:

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.

1:
2:

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.

1:
2:
3:

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!

Running queries on remote databases from SQL Server

Introduction

Usual techniques

Nice way, available starting with MSSQL 2005

Does it stop with SELECT?

Possible issues/errors

Conclusion

linked server parameter dynamic sql

SQL Server 2005

About The Author

More Articles From angelIII

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server 2005 Experts