November 17, 2015 at 8:40 am
A bit of an odd one this. I have a couple of instances of SQL Server 2012, SP1, one being Dev Edition and the other Standard Edition and both are VMs running under VMWare.
When executing a Stored Procedure to query Oracle via a Linked Server, the Standard Edition (let's call it P2) crashes SQL Server. The error message in the query window is 'A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)', however that's probably because SQL Server has crashed.
Querying the Dev Edition (let's call it U2), the query partially works but using things like DECODE and ROUND in the query also causes it to crash SQL Server.
On a third instance, again running Dev Edition (call this one D2), the same query works fine.
All three are exactly the same except for the edition difference between P2 and U2. All have 4GB of memory and all seem to have their default, out-of-the-box installation settings intact as far as I can see and all three have Oracle 9.2 clients installed and are using the Oracle Provider for OLE DB. I'm seeing absolutely nothing in the SQL Log to indicate what occurred just before the crashes however there is an entry in the App Event Log:
Faulting application name: sqlservr.exe, version: 2011.110.3128.0, time stamp: 0x50deadad Faulting module name: ntdll.dll, version: 6.2.9200.17438, time stamp: 0x55a41b15 Exception code: 0xc0000374 Fault offset: 0x00000000000ea539 Faulting process id: 0x4aa0 Faulting application start time: 0x01d121404502766c Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe Faulting module path: C:\Windows\SYSTEM32tdll.dll Report Id: 87c13341-8d33-11e5-9409-005056952126 Faulting package full name: Faulting package-relative application ID:
And I have intermittently seen other errors, most notably the following: 'Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "UBIX" reported an error. The provider reported an unexpected catastrophic failure. Msg 7305, Level 16, State 2, Line 1 Cannot create a statement object using OLE DB provider "OraOLEDB.Oracle" for linked server "UBIX".' However this error may well clear after a server reboot as it did previously.
The only piece of information that might be worth looking at so far is a reference to Distributed Transactions Role service not having been installed under the Application Server role. No idea whether that's relevant or not so I was wondering if anyone might be able to point me in the right direction.
Regards,
Gordon.
November 17, 2015 at 10:25 am
Was just about to raise a call with MS support when I came across a possible solution on their support site. I tested the fix and it works. The culprit turns out to be double-dash comment characters. Fairly innocuous when preceding a line of code in T-SQL but apparently deadly enough to crash SQL Server when passed to Oracle via a Linked Server.
The solution is to either remove any lines preceded by -- or enclose them within /* */.
The relevant article is https://support.microsoft.com/en-us/kb/2295405.
Gordon.
November 17, 2015 at 7:11 pm
Gordon Feeney (11/17/2015)
Was just about to raise a call with MS support when I came across a possible solution on their support site. I tested the fix and it works. The culprit turns out to be double-dash comment characters. Fairly innocuous when preceding a line of code in T-SQL but apparently deadly enough to crash SQL Server when passed to Oracle via a Linked Server.The solution is to either remove any lines preceded by -- or enclose them within /* */.
The relevant article is https://support.microsoft.com/en-us/kb/2295405.
Gordon.
Heh... and don't use things like an "&" in any Oracle comments either. It seems like that's a placeholder for an interactive parameter even in block quotes. The best thing to do for code passed through a linked server is to just avoid comments and other nuances.
--Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply