Alter native for 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

SQL Server 2005 Forums

Transact-SQL (2005)

Alter native for OPENQUERY

Author

Topic

sqldev6
Starting Member

18 Posts

Posted - 2008-08-07 : 23:46:42

Hi All,

Good Morning...

I am using linked server concept to access a ORACLE database from SQL server.

To retrieve the data from ORACLE db I am using the OPENQUERY funtion.

I have some data in a table variable. That is populated in SQL Server.

I need to include this table variable in the OPENQUERY's query to retrive the ORACLE data based on this table variable's data.

for Example:

EXEC('select * from OPENQUERY(linkedservername,
'select col1,col2 from db.tabl1 where col1 in (select id from @tablevariable) ')')

When i run tthe above query its giving the following error.

@tablevariable this table does not exist in the linked server.

Is there any other alternative way for this.

Could you please help me any one.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 00:26:31

you cant access data from table variable like this as its scope will be end as soon as you end batch. you would probably need permanent table or wrap the table variable script in a procedure and call it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-08 : 03:26:20

or try

select t1.* from OPENQUERY(linkedservername, 'select col1,col2 from db.tabl1 ') as t1
inner join @tablevariable as t2 on t1.id=t2.id

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources