sql-server - 字符串 ')' 后的未闭合引号 - OPENQUERY

Question

我有以下查询，使用 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

当我执行时，我收到以下错误:

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

Answer 1

如果您打印 @Query 值，您将看到错误根。所以如果你运行下面的代码:

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

你会得到以下结果:

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

现在很清楚为什么 SQL Server 会返回

Unclosed quotation mark after..

要解决这个问题，您需要记住，为了在字符串变量的输出中有一个单引号，您需要放置两个单引号。

现在你需要改写如下:

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

上述查询将产生:

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

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

您现在可以简单地将 Print 替换为 EXECUTE 命令并执行它!