CODEWITHSUNDEEP
t-sqlopenrowset
user259286
user259286

Reputation: 1005

MS T-SQL 2008: Executing an openrowset sql string will not work

Running this code

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)

Issues a syntax error Incorrect syntax near 'select z.* from openrowset('SQLNCLI', 'Server=server;UID=user;PWD=pw;', 'SELECT distinct x.PackageN'.

if I remove the parens from the (@SQL) the error changes to this: 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.

and funniest of all, if i change Execute to Print, then manually take the printed result and wrap EXECUTE around it, it works as expected and gets me my results.

I'm thinking it's some crazy single quote issue, but I can't see it.

Anyone have any ideas?

Upvotes: 2

Views: 3550

Answers (1)

Martin Smith
Martin Smith

Reputation: 454020

I just took the output of the print and pasted that into the variable assignment instead and it appears to work (at least as far as I can tell this end - at least it doesn't give any syntax errors)

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)

Upvotes: 1

Related Questions