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

Question

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?

Martin Smith · Accepted Answer · 2011-05-11 23:39:56Z

1

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)

answered May 11, 2011 at 23:39

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user259286 Over a year ago

Well that's what I said I did in my post and it works for me too.

Martin Smith Over a year ago

No you said it worked when you pasted it into the EXEC and executed it not when you pasted it into the variable assignment as my answer! If the code in my answer works I really don't see what the question is here?

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related