dynamic sql using openrowset

can someone tell me why the upper part of the dynamic sql doesn't work while the lower does? i use openrowset to connect to Oracle. thanks!

declare @.abc varchar(10)

select @.abc = 'table20070921'

execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'',
''select * from ' + @.abc + ' where flag = ''abc'') as a')

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'abc'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ') as a'.

--

declare @.abc varchar(10)

select @.abc = 'table20070921'

execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'',
''select * from ' + @.abc + ' where cust_num = 123456'') as a')

Joe,

It's hard to interpret error messages for statements you execute without ever looking at! If you substitute PRINT for execute in the upper part, you will be able to look at what you are trying to execute:

select a.* from OPENROWSET('MSDAORA','SCHEME1';'USER';'PASSWORD',

'select * from table20070 where flag = 'abc') as a

This isn't a well-formed string. Look at the openrowset sql parameter (the fifth parameter). The quoted string ends right before the abc, and that doesn't make sense.

You probably want this:

execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'',
''select * from ' + @.abc + ' where flag = ''''abc'''') as a')

You need so many quotes because the literal string 'abc' appears doubly-nested in strings.

Please be careful when you create SQL with concatenation. If users are allowed to provide the parameters, you are basically letting users write parts of your SQL program, and there is a risk someone will try to insert malicious code. Look up SQL Injection on the web for more information.

Steve Kass

Drew University

http://www.stevekass.com