Help with Embedded OpenQuery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Embedded OpenQuery

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-08-11 : 06:05:55

Background:-
We were on sql server 2005 now on 2008,.
Our 2005 server used to query other linked servers some of which are VERY old.
We cannot get 64 bit drivers/connections to one of the old servers so we have to 'bounce' our queries off another server tha we can connect to and use that as an intermediate to query the OLD server.
Now i can't seem to get an embedded OpenQuery to work, what I am trying is:-

declare @sql varchar (1000), @dt varchar(50)
set @dt = '31 JUL 2010'
set @sql = 'SELECT * FROM OPENQUERY(OKSVR,'''
+'select * from openquery(OLDSERVER,'''''
+'Select * from table1 where start_date > ' + @dt + ')'''')'''
exec (@sql)

The syntax is wrong in the above, I'm getting errors such as:-

Incorrect syntax near 'select * from openquery(OKSRV,'Select * from table1 where start_date > 31 JUL 2010)')'

I've tried multiple different ways but cannot get it to work, can antone help with this embedded query as i'm getting the quotes all wrong somehow!

Kristen
Test

22859 Posts

Posted - 2010-08-11 : 07:43:15

I do something like:


set @sql = 'select * from openquery(OLDSERVER,''Select * from table1 where start_date > ' + @dt + ''')'

set @sql = 'SELECT * FROM OPENQUERY(OKSVR,''' + REPLACE(@sql, '''', '''''') + ''')'
PRINT @sql

in order not to try to handle the humongous number of doubled-up quotes in the innermost nested parts

I would have expected you to need quotes around the concatenation of @dt in the inner query, but that depends on what format OLDSERVER expects its dates

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-08-11 : 07:57:19

Thanks,
the format for the dates is shown as set @dt = '31 JUL 2010', same for both servers.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-11 : 08:06:30

And do notice the PRINT @sql...before you execute the sql you got you need make sure that the entire syntax is correct.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Kristen
Test

22859 Posts

Posted - 2010-08-11 : 08:36:22

"the format for the dates is shown as set @dt = '31 JUL 2010', same for both servers."

Just to be, pedantically, sure

:

Select * from table1 where start_date > 31 JUL 2010

is valid and you do NOT need:

Select * from table1 where start_date > '31 JUL 2010'

??

Seems like a parsing-error waiting to happen, but if that's valid syntax then the above code should be fine

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-08-11 : 08:48:45

hmmmm

getting error now of:-

OLE DB provider "SQLNCLI10" for linked server "OKSVR" returned message "Deferred prepare could not be completed.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select * from table1 where start_date > 31 JUL 2010" for execution against OLE DB provider "MSDASQL" for linked server "OLDSERVER".
OLE DB provider "MSDASQL" for linked server "OLDSERVER" returned message "[Unify][UnifyClient ODBC Driver][UnifyClient][UnifyLNA][Unify][Unify DataServer ODBC Driver][Unify DataServer]Syntax error in SQL dynamic statement. (1060)".

Kristen
Test

22859 Posts

Posted - 2010-08-11 : 09:12:25

What did

PRINT @sql

show? Is the syntax valid if executed stand-alone? If not try adjusting it until it is and then engineer those changes back into the code that generates the dynamic SQL statement

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-08-11 : 09:32:02

print @sql =

SELECT * FROM OPENQUERY(OKSVR,'select * from openquery(OLDSERVER,''Select * from table1 where start_date > 31 JUL 2010'')')

Kristen
Test

22859 Posts

Posted - 2010-08-11 : 09:46:55

Looks fine to me.

If you can get (physically) to OLDSERVER then try running

Select * from table1 where start_date > 31 JUL 2010

and see what it says. Hopefully a more "useful" error message

If that's OK then connect to OKSVR and run

select * from openquery(OLDSERVER,'Select * from table1 where start_date > 31 JUL 2010')

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-08-11 : 09:57:31

Thanks all, got it working now, it seems the date format 31 JUL 2010 worked OK by directly querying from the old server, but I had to change it to 31/07/2010 for it to work for this piggy back.

The script helped clear up all the quote problems I had though, which was throwing me anyway.

Kristen
Test

22859 Posts

Posted - 2010-08-11 : 10:37:06

31/07/2010

Best to check that

01/02/2010

is understood as 1st Feb, and not 2nd January then.

or better still use a format that is not ambiguous - if the old-wizened server understands any such format.

one other thing that I have used is to wrap the query - which tends to stop the ODBC layer from trying to parse it (and wrongly raising an error!) and just pass it straight through instead.

So instead of

Select * from table1 where start_date > 31 JUL 2010

use

SELECT *
FROM
(
Select * from table1 where start_date > 31 JUL 2010
) AS X

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-08-12 : 03:13:14

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources