运行此代码
DECLARE @SQL VARCHAR(2500) =
'''SELECT z.* from openrowset(''''SQLNCLI'''',''''Server=server;UID=user;PWD=pwd;'''',
''''SELECT distinct x.PackageName
FROM [dw].[dbo].[dex] x
JOIN [dw].dbo.log l on l.executionid = x.SSISExecutionGUID '''') z''' EXECUTE (@SQL)
发出语法错误Incorrect syntax near 'select z.*
from openrowset('SQLNCLI', 'Server=server;UID=user;PWD=pw;',
'SELECT distinct x.PackageN'.
如果我从 (@SQL) 中删除括号,错误将更改为: The name ''select z.*
from openrowset(''SQLNCLI'', ''Server=server;UID=user;PWD=pwd;'',
''SELECT distinct x.PackageName
FROM [dw].[dbo].[dex] x join [dw].dbo.log l on l.executionid = x.SSISExecutionGUID ''
) z'' is not a valid identifier.
最有趣的是,如果我将"执行"更改为"打印",然后手动获取打印结果并在其周围包装"执行",它会按预期工作并得到我的结果。
我认为这是一些疯狂的单引号问题,但我看不到它。
有人有什么想法吗?
我只是获取了打印的输出并将其粘贴到变量赋值中,它似乎可以工作(至少据我所知 - 至少它没有给出任何语法错误)
DECLARE @SQL VARCHAR(2500) = '
SELECT z.* from openrowset(''SQLNCLI'',''Server=server;UID=user;PWD=pwd;'',
''SELECT distinct x.PackageName FROM [dw].[dbo].[dex] x
JOIN [dw].dbo.log l on l.executionid = x.SSISExecutionGUID '') z'
EXECUTE (@SQL)