Query to a linked server

[Mathieu]

Hello,

I've have quite a complex SQL Server setup. Main server is SQL Server 2008, which has a linked server defined pointing to a SQL Server 2005 Express edition.

I have a .NET app that executes stored procs on the main server, and one of the procedures uses the linked server to execute a stored proc over there.

This set up has worked for a year or so with EntityFramework 5, but now I'm trying to swap that out with Telerik Data Access. Everything is coming along nicely, except for the execution of the stored proc on the linked server. If I try to execute that, I get following error:

"The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "(my linked server name)" was unable to begin a distributed transaction".

I haven't changed anything to the database configurations or structure. Just swapping out EF5 for Telerik Data Access in my .NET code. And if I trace the command that gives the error in Telerik Data Access, and copy/paste that in SQL Management Studio with a connection on the main server, it executes without issue.

Any ideas on this. Are linked servers supported with Telerik Data Access ?

[Boyan]

Hi Mathieu,

Generally using linked servers is supported with Telerik Data Access. 

In our rest environment we have tried the following (please find a sample application and the databases ddl scripts attached):

1. On one instance of SQL SERVER we have created a database (ServerADb.sql) with a single table with two columns and a stored procedure:

CREATE PROCEDURE InsertAndSelectFromTableA
    @Id int, @title nvarchar(max)
AS
BEGIN
    BEGIN TRANSACTION
        INSERT INTO TableA(Id, Title) VALUES(@id, @title);
        SELECT * FROM TableA;
    COMMIT TRANSACTION
END
GO

2. We have linked the database server from point 1.) with another one following the steps described in this MSDN article. Please note that we have used the SA account for the link and we have set RPC and RPC Out settings to true (located in the Server Options section). We also made sure that the Distributed Transaction Coordinator service is started.

3. We have created a new database at this server (MainServerDb.sql) and we have created a second stored procedure that invokes the first one on the linked server:

ALTER PROCEDURE InsertAndSelectFromTableB_LinkedServer
    @Id int, @title nvarchar(max)
AS
BEGIN
    BEGIN TRANSACTION
        INSERT INTO TableB(Id, Title) VALUES(@id, @title);
        EXEC [SERVERNAME\SQLSERVER].[ServerADb].[dbo].[InsertAndSelectFromTableA] @id, @title
        SELECT * FROM TableB;
    COMMIT TRANSACTION
END
GO

4. We have created new Telerik Data Access model, mapped the procedure and invoked it. At this point the procedure was invoked the execution was as expected.

Following those steps we were unable to reproduce the behavior you described. Could you please try and see if the set-up attached to this post works on your side? Also could you give us more information on the steps you took to link your servers? If this does not solve the problem, could you please attach the ddl scripts required to recreate the stored procedures along with the related objects that causes an issue on your side?

We are looking forward to hearing from you.

Regards,
Boyan
Telerik

 

 

[Mathieu]

Dear,

thank you for your reply. I have not tried your attached setup yet, but I can tell you I managed to solve the issue by disabling the option in SQL Server called "Enable promotion of Distributed Transactions for RPC" on the linked server object.

When putting that to false (default = true), the query worked.

I did not have to set this on false when using Entity Framework though.

Best regards.

Mathieu

[Mathieu]

One more note: my settings for RPC is set to false, RPC out is set to true

[Boyan]

Hello Mathieu,

On our side, with the sample I sent you I was not able to reproduce the behavior you described previously regardless of the value of the Enable promotion of Distributed Transactions setting. 
Based on my research it seems that this issue could be caused by some configuration discrepancies. Please refer to this article that addresses the issue you have described previously. 

I hope this help. Do get back to us with any further questions. 

Regards,
Boyan
Telerik