OPENQUERY with a dynamic query

Simon McLaren

unread,

Jul 1, 2002, 9:19:48 AM7/1/02

to

I have come across a problem in past few days. I have a
SQL server that routinely (hourly) pulls information from
a linked server (Oracle) to update some records. The
challenge befor me is to selectively and automatically
pull from the ORACLE DB. I have begun to examine the
OPENQUERY procedure and have like the results.

My problem is that the criteria for the queries I need to
pass with OPENQUERY change on each run. So far I have not
figured out how to make the query argument dynamically
change with each run. OPENQUERY according to the
documentation does not accept any type of variables.

OPENQUERY(SERVER, 'Query') how do I build the Query
danamically, I am hoping someone has seen this problem
before and discovered a soulution or work around.

Thanks,
Simon

mike wade

unread,

Jul 1, 2002, 9:56:42 AM7/1/02

to

You will need to build the query using dynamic SQL.

EG:

DECLARE @sql varchar(4000)

SET @sql='SELECT * FROM OPENQUERY( ...........

EXEC (@sql)

>.
>

Vikrant V Dalwale [MS]

unread,

Jul 2, 2002, 9:43:10 PM7/2/02

to

hello Simon,

When you say the query changes dynamically means, only the where clause
arguments right ??
In that case you can just use the following kinda format

Select A.* from OPENQUERY(srv,'SELECT * from Table') A
Where A.col = @var

Please let me know if that helps.

Thanks,

Vikrant Dalwale

Microsoft SQL Server Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure !! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.

Norma

unread,

Jul 3, 2002, 5:49:10 PM7/3/02

to

I also need to run a query in a stored procedure against
a Sybase database that will use variables in the WHERE
clause. First I'm trying to get the query to run with
hardcoded dates, but I will need to assign the Dates at
run time.

The query works fine in Query analyzer with the 2 single
quotes are used around the fields. But when I have to put
it into a variable I get an error (Incorrect syntax
near '2002'.) If I print out this statement it is
returning single quotes. I've tried every possible
permutation of single and double quotes but can't seen to
get the concatenation out properly.

Anyone working on something like this?
Any hints would be appreciated.
Thanks
Norma

**************************************************
....
declare @mySQL varchar(500)

set @mySQL = 'SELECT * INTO ' + @sTable + '
FROM OPENQUERY(SERV01,
''SELECT SampleCode, Date, Value
FROM LIMSTST.dbo.Result
WHERE Date BETWEEN { ts ''2002-05-08 14:45:01''} AND {
ts ''2002-05-31 00:00:00'' }
AND (ParameterCode =''CL2Free'')
ORDER BY SampleDT'')'

exec (@mySQL)

***********************************************

>.
>

Erland Sommarskog

unread,

Jul 3, 2002, 6:56:03 PM7/3/02

to

[posted and mailed, please reply in news]

Norma (nb...@gvrd.bc.ca) writes:
> **************************************************
> ....
> declare @mySQL varchar(500)
>
> set @mySQL = 'SELECT * INTO ' + @sTable + '
> FROM OPENQUERY(SERV01,
> ''SELECT SampleCode, Date, Value
> FROM LIMSTST.dbo.Result
> WHERE Date BETWEEN { ts ''2002-05-08 14:45:01''} AND {
> ts ''2002-05-31 00:00:00'' }
> AND (ParameterCode =''CL2Free'')
> ORDER BY SampleDT'')'
>
> exec (@mySQL)
>
> ***********************************************

You have an opening delimiter at SELECT which you close at 2002. Let's
see, I believe you need to quadruple the quotes at that point. This
brought me as far as complaints on ANSI_NULLS and ANSI_WARNINGS not
being set, so I guess I got past the quote issues:

declare @mySQL varchar(500), @sTable sysname set @sTable = '#nnn'

set @mySQL = 'SELECT * INTO ' + @sTable + '
FROM OPENQUERY(SERV01,
''SELECT SampleCode, Date, Value
FROM LIMSTST.dbo.Result
WHERE Date BETWEEN { ts ''''2002-05-08 14:45:01''''} AND {
ts ''''2002-05-31 00:00:00'''' }
AND (ParameterCode =''''CL2Free'''')
ORDER BY SampleDT'')'

exec (@mySQL)

--
Erland Sommarskog, SQL Server MVP
som...@algonet.se
Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp