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.
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.