working with ORACLE Openquery and SQL TABLE

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

General SQL Server Forums

New to SQL Server Programming

working with ORACLE Openquery and SQL TABLE

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2009-10-09 : 11:32:29

Hi All,
i am using "SELECT * FROM OPENQUERY `to pull data form Oracle Db and table and it works fine.
now i am trying to pull all data from a specific table in Oracle that equals a specific field in my local SQL 2005 server and i am getting this erro `

". The OLE DB provider "MSDASQL" for linked server "LINKED_ORA" indicates that either the object has no columns or the current user does not have permissions on that object.``

here is the insert part of my code :

INSERT INTO TABEL1 (
TRID,
RE_ID,
EVME
)

SELECT * FROM OPENQUERY(LINKED_ORA,
'SELECT
ct.TRID,
fe.RE_ID,
fe.EVME

FROM ORA_TABLE1@db5 fe,
ORA_TABLE2@db5 ct

WHERE

ct.TRID = fe.KELD
AND lower(ct.CAT1) NOT LIKE ''%kawa%''

AND (
fe.EVME LIKE ''%Link to Known Issue''
OR fe.EVME LIKE ''%Unlink Known Issue''

)

AND ct.TRID IN (SELECT AEA.KEYID
FROM SQLTABLE1 AEA)
');

*******
I want to pull all thsese field when ct.TRID = fe.KELD
and
ct.TRID is in the SQLTABLE1

Any help is really appreciated.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 11:34:38

use a join with sql table on condition ct.TRID = fe.KELD

korssane
Posting Yak Master

104 Posts

Posted - 2009-10-09 : 11:38:24

Hi ,
thanks for your quick reply ,
How can i use the join int his case ?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 11:44:18

something like


SELECT columns...
FROM
(
SELECT * FROM OPENQUERY(LINKED_ORA, 
'SELECT 
ct.TRID, 
fe.RE_ID, 
fe.EVME 


FROM ORA_TABLE1@db5 fe,
ORA_TABLE2@db5 ct

WHERE 

ct.TRID = fe.KELD
AND lower(ct.CAT1) NOT LIKE ''%kawa%'' 


AND (
fe.EVME LIKE ''%Link to Known Issue''
OR fe.EVME LIKE ''%Unlink Known Issue''

)')
)t
JOIN  SQLTABLE1 AEA
ON AEA.KEYID=ct.TRID

korssane
Posting Yak Master

104 Posts

Posted - 2009-10-09 : 11:47:51

You mean i have to put the join outside the open query right ?
cuz open query will work only in Oracle envir.
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 11:50:11

yup..

korssane
Posting Yak Master

104 Posts

Posted - 2009-10-09 : 11:51:17

sorry to bother you visakh16 ..but it does not work either.

korssane
Posting Yak Master

104 Posts

Posted - 2009-10-09 : 11:54:00

when i am adding the join outside the openquery ,it tells me that ct.TRID could not be bound.
ct.TRID is pulled from the Oracle Db and AEA.KEYID from the SQL one..

???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 12:04:01

it should be t.

SELECT columns...
FROM
(
SELECT * FROM OPENQUERY(LINKED_ORA, 
'SELECT 
ct.TRID, 
fe.RE_ID, 
fe.EVME 


FROM ORA_TABLE1@db5 fe,
ORA_TABLE2@db5 ct

WHERE 

ct.TRID = fe.KELD
AND lower(ct.CAT1) NOT LIKE ''%kawa%'' 


AND (
fe.EVME LIKE ''%Link to Known Issue''
OR fe.EVME LIKE ''%Unlink Known Issue''

)')
)t
JOIN  SQLTABLE1 AEA
ON AEA.KEYID=t.TRID

korssane
Posting Yak Master

104 Posts

Posted - 2009-10-09 : 12:19:37

getting this error now :

(0 row(s) affected)
OLE DB provider "MSDASQL" for linked server "LINKED_ORA" returned message "[Oracle][ODBC]Restricted data type attribute violation.".
Msg 7330, Level 16, State 2, Line 61
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "LINKED_ORA".

******
i am testing only with one value and My SQLTABLE1 AEA.KEYID has only one value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 12:43:24

are you running this in oracle or sql server?

korssane
Posting Yak Master

104 Posts

Posted - 2009-10-09 : 13:02:41

SQL SERVER ..

korssane
Posting Yak Master

104 Posts

Posted - 2009-10-09 : 13:04:10

but the SQL OPENQUEY statement works like a passthrough in ORACLE DB ?