3

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 ')'.

mohamed-mhiri
  • 202
  • 3
  • 22
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
  • 4
    Can you add `PRINT @QUERY;` before the `EXECUTE` and check the rendered query with the single quote issue. – Arulkumar Jun 17 '19 at 08:33

2 Answers2

5

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!

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
  • Still getting this error after following your answer step by step : Incorrect syntax near '),[Lastname]) AS [Owner] FROM [XXX].[dbo].[Employee] ' – Amira Bedhiafi Jun 17 '19 at 09:00
  • 2
    @AmiraBedhiafi Vahid gave you the idea for the solution, but left some bugs in the code. In the third row you will need '''' instead of '' and in the fourth row you will also need '''' instead of ''. In the second row we have another bug, which is a logical bug, you need '''' instead of '' '' to have empty string instead of a space. – Lajos Arpad Jun 17 '19 at 09:16
  • 1
    @AmiraBedhiafi I have updated the answer, not it works! – Vahid Farahmandian Jun 17 '19 at 09:53
  • @LajosArpad Thanks for the great clarification – Vahid Farahmandian Jun 17 '19 at 09:53
4

You are not escaping your string quotes.

If you want to include an ' inside an string, you have to write it twice ''

So, for example, CONCAT([Firstname],'') would have to be CONCAT([Firstname],'''')

See How do I escape a single quote in SQL Server?

PS: And as @TT. has commented, in this case you will probably need to re-escape your quotes inside the openquery scope, because the openquery call will escape them again.

So CONCAT([Firstname],'') would in fact have to be CONCAT([Firstname],'''''''')

Marc Guillot
  • 6,090
  • 1
  • 15
  • 42
  • 1
    Not sure, but it might even need to be `CONCAT([Firstname],'''''''')`. It gets tricky sometimes... – TT. Jun 17 '19 at 08:36
  • I'm getting this following error : Unclosed quotation mark after the character string '),[Lastname]) AS [Owner] FROM [SMART_AMARIS].[dbo].[Employee] ' – Amira Bedhiafi Jun 17 '19 at 08:37
  • 1
    As commented on the original question, try a print @query so you will see the exact string that it's going to be executed. – Marc Guillot Jun 17 '19 at 08:39
  • 1
    @AmiraBedhiafi as TT. says, you will need to escape your quotes twice, because the openquery will also need them escaped. – Marc Guillot Jun 17 '19 at 08:44
  • 1
    Life isn't fair. The other answer was upvoted twice, even though it contains bugs, while yours was upvoted only once... – Lajos Arpad Jun 17 '19 at 09:18
  • @LajosArpad One of those upvotes is mine :-). I think he deserves it because he has written a good explanation of the problem and he has tried to give him a full solution while I have only pointed him to the right way. – Marc Guillot Jun 17 '19 at 09:21
  • 1
    @MarcGuillot the other upvote is mine, even though I found some faults in it, the overall idea was good. But that answer was written much later than yours. If there will be no correct full answer, I will have to write my own. – Lajos Arpad Jun 17 '19 at 09:29