Syntax error using openquery()

Question

Syntax error using openquery()

Jay@Work

SSCrazy

Points: 2633
More actions
December 4, 2019 at 10:35 pm

#3704544
Not quite sure what SQL is complaining about here so need some fresh eyes please. I'm trying to put the result of an openquery() into a variable. My code is:
```
declare @ram_id varchar(50),
@tsql nvarchar(1000)
set @tsql = 'select @id = service_ref from OPENQUERY([remoteserver],"select Service_Ref from [remoteDB].[dbo].[Service_Request] where service_request_number = ''2''")'

exec sp_executesql @tsql, N'@id varchar(50) OUT'
```
When I run it I get the error: Incorrect syntax near 'select Service_Ref from [remoteDB].[dbo].[Service_Request] where service_request_number = '2''
If I take that exact line and paste it (minus the outer quotes) on the remote server it executes perfectly.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I can't test the openquery part of your code but I am pretty sure that the double quotes are your problem. I also added a bit to your sp_executesql that appeared to be missing, so try this:

declare @ram_id varchar(50),
@tsql nvarchar(1000)
set @tsql = 'select @id = service_ref from OPENQUERY([remoteserver],''select Service_Ref from [remoteDB].[dbo].[Service_Request] where service_request_number = ''2'''')'
PRINT @tsql;

exec sp_executesql @tsql, N'@id varchar(50) OUT', @id = @ram_id OUTPUT;

Jay@Work SSCrazy Points: 2633 More actions · Answer 2

Almost there but....

This is the result of PRINT @tsql followed by the error

select @id = service_ref from OPENQUERY([remoteserver],'select Service_Ref from [remotedb].[dbo].[Service_Request] where service_request_number = '2'')

Msg 102, Level 15, State 1, Line 12

Incorrect syntax near '2'.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You would think with all the dynamic SQL I have to write I'd count the single quotes correctly. Try either of the set comments below, see which one works.

declare @ram_id varchar(50),
@tsql nvarchar(1000)
set @tsql = 'select @id = service_ref from OPENQUERY([remoteserver],''select Service_Ref from [remoteDB].[dbo].[Service_Request] where service_request_number = 2'')'
PRINT @tsql;
set @tsql = 'select @id = service_ref from OPENQUERY([remoteserver],''select Service_Ref from [remoteDB].[dbo].[Service_Request] where service_request_number = ''''2'''''')'
PRINT @tsql;

exec sp_executesql @tsql, N'@id varchar(50) OUT', @id = @ram_id OUTPUT;

Jay@Work SSCrazy Points: 2633 More actions · Answer 4

This works prefectly thanks!

declare @ram_id varchar(50),
@tsql nvarchar(1000)
set @tsql = 'select @ram_id = service_ref from OPENQUERY([remoteserver],''select Service_Ref from [remotedb].[dbo].[Service_Request] where service_request_number = ''''2'''''')'
--PRINT @tsql;

exec sp_executesql @tsql, N'@ram_id varchar(50) OUT', @ram_id OUTPUT;
select @ram_id