Insert Openquery HARD failure - Terminates SQL Service

Question

When executing:

insert OPENQUERY (MYSQL_Take3,  'select CustomerNumber, Token, Class, ERP_ID, JSON_Data, ImageBinary, CreatedAt, TimeSpan, TotalCount, PageID, PageCount, PageSize, GroupBy, Flag from API_DataStore where id = 0')
VALUES
( 'MyTEST', '21r6h2kfmseva0apm7c2kk8pj9', 'ART_THUMB_PAST', '8569703', NULL, NULL, N'2021-05-28T15:14:45.7567573', 'PAST', NULL, 2, NULL, 1, NULL, NULL )

I get this message

Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

MYSQL_Take3 is a valid ODBC LINKED SERVER that returns the catalog as expected.

There are TWO issues: Why is the Openquery failing? (Using standard Insert from select results in the same failure)

and -- way more important: the Server issues a DUMP and terminates its service logged in the Event Viewer as:
The .NET Framework common language runtime was shut down by user code, such as in a user-defined function or CLR type. SQL Server is shutting down. Environment.Exit should not be used to exit the process. If the intent is to return an integer to indicate failure, use a scalar function or an output parameter instead.

The DUMP stems from: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Not a graceful termination of a process.

I have researched the issue, Bing and these forums , with no useful results).

I can SELECT rows. But the INSERT always fails.

Answer 1

I finally figure it out in Cannot create an instance of OLE DB provider "MSDASQL" for linked server "MYSQL_Take3".:

https://support.microsoft.com/en-us/topic/error-message-when-you-try-to-create-an-instance-of-an-ole-db-provider-in-sql-server-cannot-create-an-instance-of-ole-db-provider-7ab8d68e-da66-54eb-564e-1989760bc9df

Thanks for your help.

Answer 2

Thanks. It now fails gracefully...

Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "MSDASQL" for linked server "MYSQL_Take3" reported an error. Access denied.
Msg 7390, Level 16, State 2, Line 4
The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "MYSQL_Take3" does not support the required transaction interface.

The MYSQL_Take3 Test Connection returns OK. A possible related to GRANT Permissions on this object in MySQL db. Odd, because I can create new rows using a small SQLCLR to do it. Truth is I do not understand: "MYSQL_Take3" does not support the required transaction interface.

Answer 3

The symptoms may be due to the ODBC driver misbehaving, corrupting SQL Server process memory. Try changing the MSDASQL provider (Microsoft OLE DB Provider for ODBC drivers) configuration to AllowInProcess 0. This will instantiate the provider outside the SQL Server process, providing additional protection.

EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'AllowInProcess', 0;