Problem with EXEC MyLinkedServer...sp_executesql within transaction

Question

I rather clueless about this so any help is appreciated.

We have got a stored procedure that is running within a transaction. The stored procedure is called from a .NET program (via normal ADO.NET).

This stored procedure has got the follwing (here simplified) code in it:

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'DELETE FROM ... WHERE ...'
--And now the important part:
EXEC MyLinkedServer...sp_executesql @sql

So it calls sp_executesql on a linked server. Most of the time this query runs fine, however sometimes the EXEC-line fails. The error message is at the end of this post.

I am not quite sure what is happening here. I am not sure if it's EXEC not working within distributed transactions or if maybe EXEC is not running within the "normal" process (whatever that is, just guessing from the error message...).

The motivation behing the EXEC-construct in this case is that we need to delete some entries from a very big table on the linked server and this way it is much faster than just writing:

DELETE FROM MyLinkedServer.DBName.TableName WHERE ...

The error message:

Original: Die Verwendung des OLE DB-Anbieters "SQLNCLI10" mit SQL Server außerhalb des Prozesses with nicht unterstützt.

Google-Translation: The use of OLE DB provider "SQLNCLI10" with SQL Server out of process ist not supported.

Answer 1

The error message refers to the OLE DB provider defined for the linked server. Hence the SQL code has no error.

The problem is the linked server is not properly defined. You should test the connection with the linked server and you will see the full error.

To fix it: recreate the Linked server setting an OLE DB provider supported in your Sql Server version. For example: 'SQLNCLI' or 'SQLNCLI11'.

To recreate a linked server:

1. Right click -> Script Linked Server as -> Create To -> New query editor window.

2. Change the OLE DB provider and the correct password (it will be scripted as @rmtpassword='########').

3. Test the connection with the linked server.

4. Try to execute again:

   EXEC MyLinkedServer...sp_executesql @sql