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.
