May 27, 2009 at 6:39 am
Hi, This is a doozy....
OK, so I have a new Oracle 10g server and a new SQL 2005 (32bit) server on Windows 2003 running as a virtual machine on VMware.
I have tried various different drivers with varying degrees of success, the only way I can get it returning data is by using Microsoft OLE for ODBC and creating a linked server using an ODBC connection I have set up.
I have already built a migration server which is running SQL server 2005 and have the linked server working, although this is using the Oracle 8i client and the connection is running via Microsoft OLE DB provider for Oracle. I've tried using this with 10g client but I understand this won't support MSDAORA, I've also tried using the Oracle Provider for OLE db, which gives me a successful connection test but only returns one record then throws the following error:
(Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "TLS2" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "TLS2".)
OK, so everything appears to work OK through the ODBC hack apart from the fact that the same query returns 7387 records on the test server (which is correct), but only 7288 records from the ODBC connection. I get no error messages from running this. Odd.
Looking into it I can see which records are missing, but when I test for a specific missing record it works fine, eg :
SELECT * FROM OPENQUERY (TLS2, 'SELECT * FROM TUK.VW_INVOICE_INTERFACE WHERE ID = 83557')
This will return a record even though record ID 83557 is missing when I take the where clause out of the above statement.
Just to preempt some questions
1) TNSPING and Oracle SQL Plus connectivity is fine and returns the correct number of records
2) I've done the registry changes needed to let the server know where the relevant Oracle dlls are for 10g
3) Running in process
4) Yep - maybe the answer is to install the Oracle 8i drivers but I'd rather fix it.
5) The results are consistent, I've tested it many times.
Any help appreciated
Thanks
Rich
May 27, 2009 at 7:41 am
OK, so I've done a couple more tests and realised it doesn't matter which table I query I get 99 fewer records through ODBC than I do through any other method.
I have had a quick look around the web and the consensus seems to be to not use ODBC, nobody seems to know why this happens. So I guess I'll have to uninstall Oracle (joy) and reinstall Oracle (joyous joy).
Although an answer would be nice...
May 27, 2009 at 7:56 am
From my experience, Using Microsoft provider (MSDAORA) gives these kind'a errors but it works perfectly when we install Oracle Client 10g (I mean ORAOLEDB) Driver and create linked server using this provider.
Also by using above provider, When you run OPENQUERY then the statement being executed should be PL-SQL since processing happens at remote server and only results are returned to SQL Server.
Try it and correct me if my understanding is wrong.
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
May 27, 2009 at 9:38 am
No joy at all with the 10g drivers - no idea why but none of it works.
Went back to the 8i drivers, everything is fine now.
That's progress for you ;(
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply