Query to a linked server

5 posts, 0 answers
  1. Mathieu
    Mathieu avatar
    21 posts
    Member since:
    Feb 2014

    Posted 02 Oct 2014 Link to this post

    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 ?



  2. Boyan
    Admin
    Boyan avatar
    100 posts

    Posted 07 Oct 2014 Link to this post

    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
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Mathieu
    Mathieu avatar
    21 posts
    Member since:
    Feb 2014

    Posted 07 Oct 2014 in reply to Boyan Link to this post

    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

  5. Mathieu
    Mathieu avatar
    21 posts
    Member since:
    Feb 2014

    Posted 07 Oct 2014 in reply to Mathieu Link to this post

    One more note: my settings for RPC is set to false, RPC out is set to true
  6. Boyan
    Admin
    Boyan avatar
    100 posts

    Posted 08 Oct 2014 Link to this post

    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
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner