Question about OPENQUERY

[SemperFiDownUnda]

I've got a Sybase 12.5 server linked to our SQLServer 2000 and need to pass slightly dynamic queries to that server

Problem is that OPENQUERY(linkedServer,'query')
doesn't let me concatinate string for the 'query'
ie I can't do
[COLOR=#80000]

SELECT * 
  FROM OPENQUERY(Sybase001,'SELECT FirstName, LastName FROM blah WHERE StartDate = ''' + CONVERT(CHAR(11),@StartDate,106) + ''' AND EndDate IS NULL')

[/color]
OPENQUERY doesn't even like having this
[COLOR=#80000]

DECLARE @sSQL VARCHAR(4000)

SELECT @sSQL = 'SELECT FirstName, LastName FROM blah WHERE StartDate = ''' + CONVERT(CHAR(11),@StartDate,106) + ''' AND EndDate IS NULL'

SELECT * FROM OPENQUERY(Sybase001,@sSQL)

[/color]

I'm missing something because I'm almost positive I've done this before but senility is setting in.....SQLSister HELP ME!

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[ClayG]

You need to put the entire Select statement into a variable and execute it (more quote doubling):

set sqlstm = 'SELECT * FROM OPENQUERY(Sybase001,''SELECT FirstName, LastName FROM blah WHERE StartDate = ''''' + CONVERT(CHAR(11),@StartDate,106) + ''''' AND EndDate IS NULL'')'

execute (sqlstm)

.. Its a very stupid limitation of OpenQuery.

 

[SemperFiDownUnda]

Thanks for the suggestion. I may have to do that (actually dumping the data to a temp table to join togther for other records. But thought I might be able to join straight to the result set and that gets messy with dynamic sql.

Anyone else?

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[SQLSister]

Books online says that OpenQuery does not accept variables. Stupid OpenQuery statement. I don;t use this much as we are an all SQL Server shop, but could you set up a linked server and use the full 4 part name of the table in a regular SQL statement? Books online suggests this as a possibility, but warns it might be slower. But slower is better than not possible.

 

[SemperFiDownUnda]

Stupid me SQLSister. Thanks the 4 part name worked great. I'll watch out for slow queries and break them up and use temp tables if need be.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first