Today I ran into a blocker when trying to do this. Having spent a reasonable amount of time taking all of my sql statements out of the OPENQUERY argument and putting them into a string variable I then found it didn't work. The reason (from BOL) it turns out is that, and I quote; "OPENQUERY does not accept variables for its arguments."
Why this is the case I haven't been able to find out so far, however there is a workaround I have discovered with thanks to this forum.
First, let's look at the statement I started with: Here I have a simple openquery statement to select everything from the table named 'Atable' in an Oracle database named 'OracleDB' (we're assuming a linked server has already been set up), for records since June 1st 2007.
select *
into MyTable
from openquery ( OracleDB
, 'select *
from Atable
where DateValue >= to_date(''20070601'', ''YYYYMMDD'')'
)
What I wanted to do was make the date sent to the Oracle database a variable so I could easily change it, so I declare this variable above the query and use it in the argument.
declare @TheDate datetime
set @TheDate = '2007-06-01T00:00:00'
select *
into MyTable
from openquery ( OracleDB
, 'select *
from Atable
where DateValue >= to_date(''' + convert(varchar(30),@TheDate,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')'
)
This doesn't work however, because . . . "OPENQUERY does not accept variables for its arguments." (See above).
The next step therefore is to put the select statement to be sent to the Oracle database in a string variable and use the date variable within this string. The variable '@MyString' is then used in the OPENQUERY statement.
declare @TheDate datetime
, @MyString varchar(max)
set @TheDate = '2007-06-01T00:00:00'
set @MyString = 'select *
from Atable
where DateValue >= to_date(''' + convert(varchar(30),@TheDate,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')'
select *
into MyTable
from openquery ( OracleDB
, @MyString
)
This again does not work however, because we are still trying to use a variable as an argument in the OPENQUERY statement, so now the trickery begins!
Immediately after setting the string variable as the select statement, it is then set again as the entire sql statement we are trying to execute, with the previously defined string used as an argument in the new string. Note that 'N' is used preceeding the string value to convert the tring to Nvarchar to eliminate any confusion around special characters.
This string is then executed without error because all variables have been converted by this point. I have included a print command in order to verify the statement that is executed, which is useful for debugging.
declare @TheDate datetime
, @MyString varchar(max)
set @TheDate = '2007-06-01T00:00:00'
set @MyString = 'select *
from Atable
where DateValue >= to_date(''' + convert(varchar(30),@TheDate,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')'
set @MyString = N'select *
into MyTable
from openquery ( OracleDB
, ''' + REPLACE(@MyString, '''', '''''') + '''
)'
print @MyString
EXEC (@MyString)
And that's how you get more cowbell from OPENQUERY
James