Article ID: 314520 - View products that this article applies to.
This article was previously published under Q314520
This article describes how to pass a variable to a linked server query.
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. For more information, see the "Using char and varchar Data" topic in SQL Server Books Online and see the following article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/156501/EN-US/ )INF: QUOTED_IDENTIFIER and Strings with Single Quotation Marks
Pass Basic ValuesWhen 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:
Pass the Whole QueryWhen 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:
Use the Sp_executesql Stored ProcedureTo avoid the multi-layered quotes, use code that is similar to the following sample:
For additional information, see the following topics in SQL Server Books Online:
Article ID: 314520 - Last Review: December 22, 2005 - Revision: 4.4