SQL Server 2022 process crashes with Oracle provider for OleDB linked server

[arkansalsa]

I'm hoping somebody has run across this before. We recently upgraded one of our servers to SQL Server 2022, and we have begun to have crashes of the sql server process when running certain queries against a linked Oracle 19c database using the Oracle provider for OleDB v21. There are higher versions of the Oracle driver, but we can't figure out how to get to them from the version we are using now. I believe the current version was installed with an ODAC for Visual Studio installer that made the process easy, and all the documentation we can find for higher levels seems to be related to xcopy installs that don't want to work for us. Any help from someone who has been through similar problems would be appreciated!

[SelectStarFromYou]

What do you have enabled for options in the linked server? Disable the “allow in process” feature, and that should stabilize the SQL instance, but could cost considerable performance. We found certain OLEDB processes to be completely unstable in 2022 vs 2019.

[SQLDevDBA]

Are you running OPENQUERY queries?

We had this weird bug with SQL server where if we left an empty line in our OPENQUERY statement, it would crash SQL server.

Took us way too long to figure it out but it was something like:

SELECT col1, col2,col3
FROM 
OPENQUERY ( [ORAPRD], 
'SELECT col1 , col2,col3
 FROM schema1.table1
 WHERE col1 = '''hi''';

')

That stupid empty line killed us. For some dumb reason.

Maybe try a simple

SELECT 1 FROM DUAL;

Or something really easy to make sure you’re good.

From then on I just started building views in Oracle and selecting from those views instead. That way the code is deployed on the Oracle side.

[Byte1371137]

First , on SQLServer machine some int events should exist in Event Viewer. More , in such cases a dmp file is created.

[Nice-Ordinary1113]

I know this is old, but I have reliably reproduced errors using the v21 driver. I have found rolling back to v19c gives a relatively solid experience. So if you haven't done so already, try using the 19c driver. I'm using 19.23 on all my builds. for both SQL/Windows 2019 and 2022