Making dynamic queries for linked server in SQL Server

Question

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 sp_executesql.

I am getting the following error

Syntax error in SQL statement. Syntax error line 1 at or after token .[10179].

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'', ''''@comp'''' with no luck. Any help is greatly appreciated.

if you use "SET QUOTED_IDENTIFIER OFF" then you can delimit the string query using double quotes instead of single quotes. This doesn't solve your issue but it does prevent you from having to escape one set of your single quotes. — DarrenMB
– DarrenMB, Commented Oct 3, 2017 at 18:27
Could you try with EXEC LINKSERVER12.master.dbo.sp_executesql N'SELECT id from company where name = @comp', N'@comp nvarchar(50), @csid nvarchar(MAX) OUTPUT', @comp = @company,@csid = @id OUTPUT ? Something similar to it. It allows you to avoid multi-layered quotes. — crucifery
– crucifery, Commented Oct 3, 2017 at 18:30

Novice Programmer · Accepted Answer · 2017-10-06 08:51:52Z

2

Just build the string query wihtout parameters.

USE [companyDB]
GO

DECLARE
    @companyName AS nvarchar(50)
    ,@id nvarchar(MAX)
    ,@query NVARCHAR(MAX)

SET @companyName = 'AMAZON'

DECLARE @idTable TABLE
(
    id INT
)

--Repace Server, UID and PWD
SET @query = 
N'SELECT 
    [id]
FROM OPENROWSET
(
    N''SQLNCLI''
    ,N''Server=10.111.1.111;UID=username;PWD=password123;''
    ,N''SELECT [id]
       FROM [companyDB]
       WHERE [name] = '''''+@companyName+'''''''
)'

INSERT INTO @idTable
EXECUTE (@query)

SELECT TOP 1
    @id = id
FROM @idTable

edited Oct 6, 2017 at 8:51

answered Oct 6, 2017 at 8:38

Novice Programmer

412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Making dynamic queries for linked server in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related