Linked Server oracle 0x80004005 error
-
Linked Server oracle 0x80004005 error
Has anyone gotten around the ......
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ]. ....
error when creating a linked server to oracle using the oracle drivers ?
Have no problem with the linked server ms oracle driver other than the fact
that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
plus.
Any ideas greatly appreciated ! Thanks, Steve.
-
We don't run Reporting Services, but we do quite a bit of heterogeneous queries against Oracle DBs.
I don't use the Oracle OLEDB for Linked Servers, because I don't believe it's supported. The only reason I've seen for queries to appear slow is due to the entire select data set being returned for filtering by MS SQL. My guess is this caused by incompatible collation.
Our work-around is to use OpenQuery to pass the query to Oracle for native processing via Oracle sql statement. SELECT * FROM OPENQUERY(linkedservername, 'SELECT COL1 FROM schema.table WHERE COL1 = ''sometext'' ') --notice that the statement is quoted, which requires that the criteria quotes be escaped. Also the OpenQuery can be joined to MS SQL tables and run very efficiently.
-jerry
-
Thanks for the reply Jerry ! Need to do this in a stored procedure. Will test it out shortly and get back to you. This would be a HUGE benefit to us if it works. Thanks, Steve.
-
Should work, unless in you try to wrap it in a transaction, which the provider doesn't support. Good luck.
-
Hi Jerry,
Am trying to connect to an Oracle 8i server using openquery but can not get around
........
An error occurred while preparing a query for execution against OLE DB provider 'OraOLEDB.Oracle'.
[OLE/DB provider returned message: ORA-00904: invalid column name]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare:
repare returned 0x80040e14].Crazy because this is a very simple...
SELECT * FROM OPENQUERY(VMFO, 'SELECT ID FROM SYSADM.PART WHERE ID="SJ3463"')
-
...doesn't seem to like the column name. Is ID a reserved word in Oracle? Try quoting it.
-
In sql talk the query did not run until......
SQL> SELECT ID FROM PART WHERE ID = 'SJ3463';
ID
------------------------------
SJ3463
so then it was just a matter of playing with it until......
SELECT * FROM OPENQUERY(VMFO, 'SELECT ID FROM PART WHERE ID = ''SJ3463''')
SJ3463
Thanks again !!! Just my misreading of your email and the " vs '' (boy they look so close).
Are you passing parameters too ?
-
I always assumed the literal effect of the quoted query would nix that idea. Let me know if you get it to work. Probably have to switch back to linked server query for that. BTW, I have seen a 'like' clause (in a linked server query) filter on the Oracle side, where a '=' would not...go figure.
-
1 year later and it still works. Easy way to recreate a table with 200+ columns without typing using a linked server. I also use software(delphi) and oracle client oledb to move the data but needed the linked server to build the table. Everyone else probably already knew this but here anyway:SELECT * into NewSQLTable FROM OPENQUERY(
OracleLinkedservername, 'select * from OracleCatalog.OracleView
where last_name = ''xyz'' ') My thanks to Jerry
-
New tip...
I ran across this article on passing a variable to an openquery a while back...
http://www.sqljunkies.com/How%20To/BA89A311-B3AE-4803-BABA-64FCF1E1F8F7.scuk
...and recently had the need to use the tip to improve the performance of an openquery by passing a variable into the Where clause on the Oracle side. It took me a little bit to get all the nested quotes right, but the performance gain was well worth it!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply