openquery syntax

mpankuj · October 8th, 2004, 02:30 AM

hi,
i am using openquery to write the select statement,that query i am giving below..but it is

giving syntax error..:(

EXEC ('SELECT * FROM OPENQUERY ( ITCCUST, ''SELECT REF_NO7 from GL_BATCHDETAILACCT where

PERIOD_NO>=' ' ' ' ' +@StartPeriod+' ' ' ' ' +and PERIOD_NO<=+ ' ' ' ' '+@EndPeriod+' ' ' ' '

+and FISCAL_YR>=+' ' ' ' ' +@StartYr+' ' ' ' ' +and FISCAL_YR <=+' ' ' ' ' +@EndYr+' ' ' ' ' '

' )')

can anybody tell me the write syntax..
thanks in advance:)

madhukp · October 8th, 2004, 03:00 AM

What do you mean by this delimiter ' ' ' ' ' ?

AFAIK, you need only use ' and ' with any possible escape character while using openquery.

I have not used the arguments passed to the query like @StartPeriod. I doubt whether this is possible.

joefawcett · October 8th, 2004, 01:25 PM

The query must be a string literal, it can't be built up dynamically. You need to filter outside the openquery part.

Code:

SELECT * FROM OPENQUERY(<main select here>) WHERE ...

--

Joe

happygv · October 8th, 2004, 08:48 PM

Did you check on BOL? Here is the syntax...

Quote:

quote:Syntax

Code:

OPENQUERY(linked_server, 'query')

Arguments
linked_server
Is an identifier representing the name of the linked server.

'query'
Is the query string executed in the linked server.

Examples
This example creates a linked server named OracleSvr against an Oracle database using the Microsoft OLE DB Provider for Oracle. Then this example uses a pass-through query against this linked server.

Note: This example assumes that an Oracle database alias called ORCLDB has been created.

Code:

EXEC sp_addlinkedserver 'OracleSvr', 
    'Oracle 7.3â, 
    'MSDAORA', 
    'ORCLDB'
GO

SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles') 
GO

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

iisager · September 25th, 2006, 09:09 AM

What about
select * from mytable where item like ('A','B')

That's a problem for me, how do i type it

open OPENQUERY(xxx,'select * from mytable where item like ('A','B')')

joefawcett · September 25th, 2006, 09:44 AM

OPENQUERY replaces a table so you need something like:

Code:

SELECT * FROM OPENQUERY(xxx,'select * from mytable ') where item in ('A','B')

--

Joe (Microsoft MVP - XML)

whiteshadow · January 15th, 2007, 03:17 AM

Does this mean if I have a million of data from the linked server, it would first be transported to where my code executes then after that it would filter the transferred data?

A million of records first be transported to my server before the where clause would take an effect?

Is this correct?

whiteshadow · January 15th, 2007, 03:18 AM

And also does anyone knows how to optimize this? Is there another work around on this?

whiteshadow · January 17th, 2007, 04:03 AM

Hi,

I got a solution for my problem. I used SP_EXECUTESQL of SQL server.
Just incase you want to know how I solved my problem. :)

Code:

DECLARE @sql NVARCHAR(4000)
DECLARE @number INT
SET @number = 1

SET @sql = 'SELECT * FROM OPENQUERY(TEST_LINK, 
          ''SELECT * FROM table1 
          WHERE  stringField = ' + '''''' + convert(varchar,@number) + '''''' + 
          ' AND numericField = ' + convert(varchar,@number) + ' '')'
PRINT @sql

EXECUTE SP_EXECUTESQL @sql

Just take note of the single quotes inside the query (@sql).
Hope this helps. And anyone who has a better solution please let me know.

Thanks.

happygv · January 17th, 2007, 06:33 AM

You can also replace

Code:

EXECUTE SP_EXECUTESQL @sql

with

Code:

Exec (@sql)

Cheers.

_________________________
- Vijay G
Strive for Perfection