Pass variable into openquery statement?

[dswitzer]

Is it possible to pass a variable into the select statement going to Oracle through an openquery statement?

In QueryAnalyzer, I want to:

1. read the max date in sqlserverTableA
2. delete from sqlserverTableA where date =max_date
3. select and input data from oracle_table where date>=max_date

Here is what I have so far:

DECLARE @maxdate VARCHAR(8)
begin
SET @maxdate = (select max(call_day) from TableA)
set @maxdate = @maxdate - 1
end

delete from TableA where call_day >=@maxdate

select * from openquery (connections,'select * from oracle_table
where call_day >=' + @maxdate + ')


It must be a erroneous concat issue - but I am not seeing it. The messages I get are:

Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near '+'.
Server: Msg 105, Level 15, State 1, Line 21
Unclosed quotation mark before the character string ' )
'.

Any ideas?

 

[nread]

select * from openquery (connections,'select * from oracle_table
where call_day >='' + @maxdate + ''')

Try that, need quotes around quotes...

 

[dswitzer]

Thanks - but no dice.

I tried:
select * from openquery (connections,'select * from oracle_table where call_day >=" + @maxdate + " ')


Server: Msg 7321, Level 16, State 2, Line 7
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00904: invalid column name
]


I tried:
select * from openquery (connections,' "select * from oracle_table where call_day >=" + @maxdate + ')

Server: Msg 7357, Level 16, State 2, Line 7
Could not process object '"went on to list the SQL statement.....

Darn.....must be some combination of these things that will work......

 

[nread]

The option i gave you should work, the error message you got relates to a colum in your select.... There is obviously a mistake with the name... Check it out....

 

[dswitzer]

Appreciate your help.

No error in select statement that I can see.

Real SQL:
select * from openquery (dw1,'select * from call_detail
where rownum <5 and call_day =20030614' )
This runs - no problem.

When I run this version:

DECLARE @maxdate VARCHAR(8)
begin
set @maxdate = 20030614
end
print @maxdate
select * from openquery (dw1,'select * from call_detail
where rownum <5 and call_day =&quot; + @maxdate + &quot;' )

It compiles fine, but errors out:
Server: Msg 7321, Level 16, State 2, Line 6
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00904: invalid column name
]

I'm not using any different columns, so I'm guessing (with my limited knowledge) that it is reading the @maxdate as a column -- as the phrase + @maxdate + is still red (like text) -- when I'm guessing it should be grey/black)...

 

[nread]

Make sure you use single quotes ' and not &quot; in all instances....

 

[dswitzer]

We were on the right track -- just a few quotes shy....


DECLARE @TSQL varchar(8000), @VAR char(8)
SELECT @VAR = '20030614'
SELECT @TSQL = 'SELECT * FROM OPENQUERY(dw1,''select * from call_detail where rownum <5 and call_day = ''''' + @VAR + ''''''')'
EXEC (@TSQL)

From MSKnowledgeBase

Thanks nread.