Unclosed quotation mark after the character string ')' - OPENQUERY

Question

I have the following query, executed with OPENQUERY :

    DECLARE @DOMAIN NVARCHAR = N'XXX'
    DECLARE @QUERY NVARCHAR(MAX)= '
    SELECT * FROM OPENQUERY( [XX\XX],''

    SELECT  CONCAT(CONCAT([Firstname],''),[Lastname]) AS [Owner]
           FROM [Employee] '')'
    EXECUTE XX_executesql @QUERY

When I execute , I get this following error :

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'. Msg 105, Level 15, State 1, Line 5 Unclosed quotation mark after the character string ')'.

Can you add PRINT @QUERY; before the EXECUTE and check the rendered query with the single quote issue. — Arulkumar

Vahid Farahmandian Vahid Farahmandian · Accepted Answer · 2019-06-17T08:45:23

If you print the @Query value, you will see the error root. So if you run the following code:

DECLARE @DOMAIN NVARCHAR = N'XXX';
    DECLARE @QUERY NVARCHAR(MAX)= '
    SELECT * FROM OPENQUERY( [XX\XX],''
    SELECT  CONCAT(CONCAT([Firstname],''),[Lastname]) AS [Owner]
           FROM [XXX].[dbo].[Employee] '')'

PRINT @QUERY

You will get the following result:

SELECT * FROM OPENQUERY( [XX\XX],'
SELECT  CONCAT(CONCAT([Firstname],'),[Lastname]) AS [Owner]
       FROM [XXX].[dbo].[Employee] ')

Now it is clear why SQL Server returns the

Unclosed quotation mark after..

To solve it you need to keep in mind that, in order to have a single quote mark in the output in a string variable, you need to put two single quote mark.

Now you need to rewrite it as below:

DECLARE @DOMAIN NVARCHAR = N'XXX';
DECLARE @QUERY NVARCHAR(MAX)= '
SELECT * FROM OPENQUERY( [XX\XX],''

SELECT  CONCAT(CONCAT([Firstname],''''),[Lastname]) AS [Owner]
       FROM [XXX].[dbo].[Employee] '')'

PRINT @QUERY

aforementioned query will produce:

SELECT * FROM OPENQUERY( [XX\XX],'

SELECT  CONCAT(CONCAT([Firstname],''),[Lastname]) AS [Owner]
       FROM [XXX].[dbo].[Employee] ')

You can now simply replace Print with EXECUTE command and execute it!

Unclosed quotation mark after the character string ')' - OPENQUERY

2 Answers