2

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.

3
  • Can you post the definition of the linked server (obfuscated user and password, of course)?
    – Lmu92
    Oct 11, 2014 at 13:08
  • Also, could you provide the error code. This way, we can look up the exact English wording of the error. Oct 12, 2014 at 10:49
  • @ManOnAMission - (for troubleshooting) see if it works by running the code within a Query Window on the SQL Server being connected to (where the Linked Server "MyLinkedServer" is defined). This assumes you have access to the SQL Server that the .Net application connects to. If this works, the issue is only from the application. Check out this link: Permissions needed to set up linked server with out-of-process provider
    – user50166
    Oct 13, 2014 at 4:22

1 Answer 1

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
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.