I want to create a dynamic query in SQL Server which will run on linked server. I am trying to do it as follows.
USE [MYDB] GO DECLARE @company AS nvarchar(50); DECLARE @id nvarchar(MAX); DECLARE @query nvarchar(MAX); SET @company = 'mycompany.com'; SET @query = N'SELECT @csid = id FROM OPENQUERY(LINKSERVER12, ''SELECT id from company where name = @comp'')'; EXECUTE sp_executesql @company_query, N'@comp nvarchar(50), @csid nvarchar(MAX) OUTPUT', @comp = @company,@csid = @id OUTPUT
In the above script, I want to pass the value for
@comp dynamically. For that I tried setting input and output variable while executing SQL with
I am getting the following error
Syntax error in SQL statement. Syntax error line 1 at or after token ..
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query "SELECT id from company where name = @comp" for execution against OLE DB provider "MSDASQL" for linked server "LINKSERVER12".
The error is happening at the dynamic query
N'SELECT @csid = id FROM OPENQUERY(LINKSERVER12, ''SELECT id from company where name = @comp'')'
I tried replacing
@comp in the SQL query with '
''''@comp'''' with no luck. Any help is greatly appreciated.