Link to home
Start Free TrialLog in
Avatar of swardwell
swardwell

asked on

Using Variables in Openrowset Passthrough SQL Statement

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.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i hope i didn't mess up the ' and '' and ''' ... :-)
Avatar of swardwell
swardwell

ASKER

This worked great!