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 |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2002-03-06 : 01:33:45
|
| hi guys... i need to retrive the data from oracle and selection cretria is sql data.... examle..t006_subscriber is oracle table...unit is sql table both the tables has account_id common... a guess...select * from openquery(pmbs,"select distinct owner_id from t006_subscriber where account_id in (" & select distinct account_id from unit &") i dont know how it can .... i tried out in diffrent wayselect owner_id from pmbs...mbs.t006_t006_subscriber where account_id in(select acount_id from unit)but i get a error...OLE DB provider 'MSDAORA' supplied invalid metadata for column 'SUBSCRIBER_REF'. The data type is not supported.'SUBSCRIBER_REF' is number datatype in oracle table....thanks in advance======================================Ask to your self before u ask someone |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2002-03-06 : 04:00:48
|
"OLE DB provider 'MSDAORA' supplied invalid metadata for column 'SUBSCRIBER_REF'. The data type is not supported" problem solved by installing the patch sp3..but the problem is bit diffrent now ...i am not able to get the entire base....any suggestion welcome... ======================================Ask to your self before u ask someone |
 |
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-03-06 : 21:29:38
|
| Well, there are a couple of options. I don't know how to help with the four part names, but to use OPENQUERY, you have to have a string literal with no variables. In other words, you have to build your entire SELECT * FROM OPENQUERY() inside a string, and then use EXEC() to execute it.To be honest, it also depends on the size of your tables. I wouldn't use this approach unless your local table is small.--You can build a CSV usingDECLARE @CSV varchar(some huge number)SET @CSV = ''-- this is going inside another string, so you have to have double-- single quotes for each double single quote.SELECT @CSV=@CSV + '''''' + CAST(YouColumn AS VARCHAR(some number)) + ''''', 'FROM YourTableSET @CSV = LEFT(@CSV, LEN(@CSV)-2) -- Double check this.PRINT @CSV-- From here, you can start on the main queryDECLARE @Qry varchar(even larger number)SET @Qry = 'SELECT * FROM OPENQUERY(pmbs,''select distinct owner_id from t006_subscriber where account_id in (' + @CSV + ')'')'-- Test your string again. You can copy and paste from the messages-- window into a new analyzer window to debug syntax. When ready, you-- can uncomment the EXEC() linePRINT @Qry --EXEC(@Qry)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
|
|
|
|
|
In clause for Openquery( )