We help IT Professionals succeed at work.

Using Variables in Openrowset Passthrough SQL Statement

swardwell
swardwell asked
on
I am using OPENROWSET with MSDAORA to return rows from an Oracle 8.1.6 table to SQL Server 7.  Because the Oracle tables are so large,  I would like to dynamically provide criteria for returning the rowset.
 
Example
 
I declare a variable
 
declare @area_office varchar(11)
 
I populate the variable from a table

set @area_office =  (select area_branch from branches where area_branch_code = 1)

I want to concatenate the string constant with the variable and pass it through to Oracle
 
select a.* from openrowset('MSDAORA', 'fff','user';'pwd', 'select * from tablename where branch = ''' + @area_office + '''') as a
 
I get an error 170 "Incorrect syntax near '+'  " and I have been unable to resolve this error.   If I bring the condition outside the openrowset I am able to run the statement; however, I want to limit rows coming across the network so I would like the criteria set in the Oracle SQL statement.
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
YOu cannot use variables for the openrowset statement AT ALL :-(

The only workaround i found until now:
EXEC ('select a.* from openrowset(''MSDAORA'', ''fff'',''user'';''pwd'', ''select * from tablename where branch = '''''
+ @area_office + ''''') as a')

Cheers


Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
i hope i didn't mess up the ' and '' and ''' ... :-)

Author

Commented:
This worked great!