When you query a linked server, you frequently perform a pass-through query
that uses the OPENQUERY, OPENROWSET, or OPENDATASOURCE statement. You can view
the examples in SQL Server Books Online to see how to do this by using pre-defined
Transact-SQL strings, but there are no examples of how to pass a variable to
these functions. This article provides three examples of how to pass a variable
to a linked server query.
To pass a variable to one of the pass-through functions, you must build a dynamic
query. Any data that includes quotes needs particular handling.
Pass Basic Values
When the basic Transact-SQL statement is known, but you have to pass in one
or more specific values, use code that is similar to the following sample:
DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT @VAR = 'CA'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors
WHERE state = ''''' + @VAR + ''''''')'
EXEC (@TSQL) |
Pass the Whole Query
When you have to pass in the whole Transact-SQL query or the name of the linked
server (or both), use code that is similar to the following sample:
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000),
@LinkedServer nvarchar(4000)
SET @LinkedServer = 'MyLinkedServer'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')'
EXEC (@OPENQUERY+@TSQL) |
Use the Sp_executesql Stored Procedure
To avoid the multi-layered quotes, use code that is similar to the following
sample:
DECLARE @VAR char(2)
SELECT @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
N'@state char(2)',
@VAR |
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server 7.0 (Version: 7.0)
© 2003 Microsoft