August 29, 2011 at 9:47 am
Hello,
I've set up a linked server from SQL 2008 R2 to Oracle using the OraOLEDB.Oracle provider. I'm using this linked server to periodically query tables in Oracle and call stored procedures (also in Oracle) to pass data back. Occasionally, when I call these stored procedures within SSMS, the query hangs and eventually the SQL service crashes, bringing down the database.
Oddly enough, if I restart the SQL Service and execute the same query to run the same stored procedure in Oracle again, it works. When it works, it only takes a few seconds to execute. I haven't been able to consistently reproduce this issue but can usually have it happen after attempting to call any stored procedures in Oracle 5-10 times. I'm going to attempt to get someone on the Oracle end to run a trace to see what happens when the crash occurs.
Here's the properties for the linked server that I've set up:
Collation Compatible: False
Data Access: True
RPC: True
RPC Out: True
Use Remote Collation: False
Collation Name: (blank)
Connection Timeout: 200
Query Timeout: 200
Distributor: False
Publisher: False
Subscriber: False
Lazy Schema Validation: False
Enable Promotion of Distributed Transactions: False
In the provider settings for OraOLEDB.Oracle, I've set 'Allow In Process' to True.
Here's a sample of how I'm actually calling the Oracle sproc:
EXECUTE('begin oracle_package.oracle_sproc(?,?); end;',
@PARAM_1,
@PARAM_2) AT LINKED_SERVER
September 2, 2011 at 8:11 am
k_poth (8/29/2011)
I've set up a linked server from SQL 2008 R2 to Oracle using the OraOLEDB.Oracle provider. I'm using this linked server to periodically query tables in Oracle and call stored procedures (also in Oracle) to pass data back. Occasionally, when I call these stored procedures within SSMS, the query hangs and eventually the SQL service crashes, bringing down the database.Oddly enough, if I restart the SQL Service and execute the same query to run the same stored procedure in Oracle again, it works. When it works, it only takes a few seconds to execute. I haven't been able to consistently reproduce this issue but can usually have it happen after attempting to call any stored procedures in Oracle 5-10 times. I'm going to attempt to get someone on the Oracle end to run a trace to see what happens when the crash occurs.
Tracing session on Oracle side wouldn't hurt but I do not think is going to show anything related to SQL Server side crash.
It looks to me like this is an SQL Server side issue - Oracle database may not even know what's going on.
Any error message on Windows/SQL Server logs?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 2, 2011 at 8:33 am
Also, which oracle-client did you install on the ms sql server?
September 2, 2011 at 9:07 am
I believe that my client had installed Oracle11g. I've actually got this same issue occurring on two separate SQL boxes at two different client sites. Although they are connecting to the same Oracle DB.
When the crash does occur, not much happens in management studio. Usually something along the lines of this:
Msg 2, Level 20, State 0, Line 0
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I've attached the Windows Error Report file generated by the crash, but I'm not sure that it helps at all. Same thing with the SQL Error log. The only event that I see when the crash occurs in the Windows Application log is a generic 'The MSSQLSERVER service terminated unexpectedly'. There's isn't anything leading up to that.
September 2, 2011 at 9:07 am
Sorry, here are the attachments.
October 7, 2011 at 3:03 pm
Hi folks, I'm have the exact same problem as k_poth.
I'm using Windows 2008 Enterprise and SQL Server 2008 R2. I'm using the 11G Release 2 Client Drivers, and whenever we execute a store procedure or query against a linked server (same query works on another box running Windows 2003 + SQL 2000) it crashes MSSQL 2008.
Looking at poth's errors, I have the same error c0000374 which appears to be an issue with the heap manager?
Here's a link I found about a faulty extended stored procedure causing a similar issue? http://blogs.msdn.com/b/sqlserverfaq/archive/2011/05/24/inf-sql-server-extended-stored-procedures-amp-behaviour-change-starting-with-windows-2008.aspx
Has anybody had any luck fixing this yet?
March 30, 2012 at 8:57 am
Hope you solved this issue. If yes, was it the running of OLEDB Oracle provider in process that was crushing the SQL Server?
March 30, 2012 at 9:03 am
Turned out to be my own stupidity with the install process of SQL 2008 and Windows 2008.
What I failed to do with the Windows 2008 install was install Distributed Transactions as a Role through Server Manager. Once I did this properly our issues with Joins between the remote Oracle server and our local SQL Server table went away.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply