Source Object Failing to Initialize for MSDAORA Provider

Question

Source Object Failing to Initialize for MSDAORA Provider

Anthony Fourie

Old Hand

Points: 347
More actions
July 6, 2012 at 5:10 am

#260808

Hi Guys,
I'm at a loss with this one:
Problem/Error:
I have a job that executes a stored procedure in a database
This stored proc accesses an Oracle linked server and just selects data from an Oracle Table
The problem is that this proc has been failing returning vague errors, that I unable with my experience to follow up on, I need a help with regards to how Linked Servers are working in the background, where I should be looking to get this sorted out, and any pointers on what to be aware of when setting up these linked servers.
Background Info:
This job ran successfully time and time again and has just recently begun failing
The user account utilized by the linked server has more than sufficient privileges on the Oracle Database
The SQL Server Version is: SQL Server 2005 Standard Edt. 9.00.4035.00
The SQL Server Architecture: x86
The Windows Version is: Windows Server 2003 R2 Enterprise Edt. SP2 Build 3790
The Windows Architecture is: x86
Installed CPU: AMD Opteron 275 2.21GHz
Installed RAM: 4GB
The Oracle Version is: 10.2.0.4
The Oracle OS Architecture: x64
The Oracle Database is setup in a RAC configuration (I take it that's a form of clustering)
Tests:
I have checked that the tnsnames.ora file is correct (Entries are correct)
I can TNSPING <Server> without issue
I can successfully test the connection to the Oracle Linked server using the Linked Server 'Test Connection'
I have tested both 4 part naming and OPENQUERY syntax's to try and query the linked server (Not one works)
When I executed the job, I get the following output:
Executed as user: DOMAIN\service-sql. The OLE DB provider "MSDAORA" for linked server "LNKSERV" reported an error. The provider did not give any information about the error. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "IBOSS105". [SQLSTATE 42000] (Error 7303). The step failed.
When executing the stored proc directly through query window, I get the following:
Msg 7399, Level 16, State 1, Procedure refresh_data, Line 5
The OLE DB provider "MSDAORA" for linked server "LNKSERV" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Procedure refresh_data, Line 5
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "LNKSERV".
I was getting the following error when I tried to execute the SQL Server Agent Job:
Start failed for Job 'refresh_data'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Unable to post notification to SQLServerAgent (reason: MapViewOfFile() returned error 8, 'Not enough storage is available to process this command.') (.Net SqlClient Data Provider)
Error Number: 22022
Severity: 16
State: 1
I have no idea what is causing the problem and this is affecting business... Could anyone please help me with a little push in the right direction?? I hope the information that I provided is enough, if there is something else you need let me know and I will do what I can to get it. I understand the x86 to x64 causes problems, though my question remains why it was working a day ago, but now causing failure... Any help is appreciated. 🙂
Thanks,
Anthony
/Ants

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Same reply as on #sqlhelp:

Have you tried dropping and re-adding the linked server? I hope that doesn't fix it but it would be the next step that I would try.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Anthony Fourie Old Hand Points: 347 More actions · Answer 2

Another employee went and bounced the server which seems to have solved the issue for now, though

(As I said on Twitter) I doubt that will solve the underlying issue.

Will take a look at the link that you sent me:

http://connect.microsoft.com/SQLServer/feedback/details/187192/openquery-to-linked-server-hangs-leaving-spid-with-open-tran-that-cannot-be-killed-then-templog-ldf-grows-without-limit-requires-sql-server-restart-on-production-servers

The workarounds look like they might apply to the SQL Server 2012 installation I have at the same client (They installed). Similar problem happened, now busy with Oracle Support (regarding the 2012 server) to determine if the actual problem resides in the x64 Oracle driver for OLE DB.

Thanks for the reply...

If I manage to get it solved I will post it back here, though until then any suggestions/recommendations

will be welcome 😀

/Ants

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

MSDAORA is an old driver for Oracle.

Try OracleOleDB or the Attunity drivers