OpenQuery using parameters

16 Dec 2005 1:18 AM

myob@beatles.com

I need a little help with using parameter values in the where clause
with the OpenQuery statement.
What would the syntax for the following be if 10 was a variable? And
does the syntax change depending on the datasource? In my case I am
querying an Oracle database. But I can't get it work on an Oracle or
Access database.
SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
WHERE deptno = 10'
Many thanks to the answer for this.
johnny
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet

16 Dec 2005 3:49 AM

Uri Dimant

Johnny

Did you create a linked server to the Oracle database?

BOL has some examples how to create a linked server to Oracle adatabase.

SELECT * FROM OracleServer.Database.dbo.Table WHERE blblbl

Does the aboe example work for you?

For Access database you can use OPENDATASOURCE command

SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers

"Johnny" <m***@beatles.com> wrote in message

news:PBoof.631530$_o.29628@attbi_s71...
Show quote

>I need a little help with using parameter values in the where clause
> with the OpenQuery statement.
> What would the syntax for the following be if 10 was a variable? And
> does the syntax change depending on the datasource? In my case I am
> querying an Oracle database. But I can't get it work on an Oracle or
> Access database.
> SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
> WHERE deptno = 10'
> Many thanks to the answer for this.
> johnny
> -- Posted with NewsLeecher v3.0 Beta 6
> -- http://www.newsleecher.com/?usenet