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.
| Author |
Topic |
|
korssane
Posting Yak Master
104 Posts |
 Posted - 2010-08-23 : 14:28:02
|
| Hi folks,i am really stuck in here with this . i want to show some data from an oracle db using the openquery statment. this data is should be pulled based on a Local table field info. here is my small example but it does not work .SELECT * FROM OPENQUERY(LINKED_A, 'SELECT fe.KEYFIELD FROM table1@oradb fe ') as b JOIN sql2k5db AEA ON b.KEYFIELD = AEA.KEYFIELDN.B : The open query statement works fine separately.thanks for the help |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 14:32:51
|
| is @oradb a variable from where you need extract table name?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
korssane
Posting Yak Master
104 Posts |
Posted - 2010-08-23 : 15:19:58
|
| hi it is a dbname / schema basically but this can be ignored ..i can write my code like this :SELECT * FROM OPENQUERY(LINKED_A,'SELECT fe.KEYFIELD FROM table1 fe ') as bJOIN sql2k5db AEA ON b.KEYFIELD = AEA.KEYFIELDthanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-23 : 15:24:37
|
| ok...if you need to use variable then you need dynamic sql------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
korssane
Posting Yak Master
104 Posts |
Posted - 2010-08-23 : 15:31:53
|
| what do you mean by dynamic variable.. and why do i need this ?is is not like simple join between a local SQL table and another remote one ?thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-24 : 07:34:14
|
quote:
Originally posted by korssane
what do you mean by dynamic variable.. and why do i need this ?is is not like simple join between a local SQL table and another remote one ?thanks
i was telling dynamic sql not dynamic variable. if part of table name comes from variable, you need to write statement likeEXEC('SELECT * FROM ' + @Variable + '..Table')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-08-24 : 08:29:36
|
| Also make sure to read thiswww.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
HOW to use OPENQUERY WITH SQL LOCAL TABLE ??