Post reply

Error running stored procedure after insert trigger

  • November 22, 2018 at 2:18 am

    #382328

    Hi All,

    I have a very simple stored procedure that inserts 2 values to a linked database table.


    ALTER PROCEDURE [dbo].[InsertCustIDRow]
        -- Add the parameters for the stored procedure here
        (@CustID NCHAR(5))
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    INSERT INTO Remsvr.Logistics.dbo.CustomerLogs
        ([CustomerID]
        ,[Action])
      VALUES
        (@CustID
        ,'Inserted')
    END

    When I run the stored procedure from a query window in SSMS, no problem it works without any issues.
    However when the stored procedure is run from an after insert trigger 


    USE [TestDB]
    GO
    /****** Object: Trigger [dbo].[Customer_INSERT]  Script Date: 22/11/2018 09:03:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[Customer_INSERT]
       ON [dbo].[CustomerTest]
    AFTER INSERT
    AS

         EXEC InsertCustIDRow 'Test7'

    I get the following error...


    No row was updated.

    The data in row 4 was not committed.
    Error Source: Net SqlClient Data Provider.
    Error Message: The operation could not be performed because OLE DB provider "SQLNCL11" for linked server "Remsvr" returned message "No transaction is active.".

    Correct the error and retry or press ESC to cancel the change(s).

    If I change the stored procedure to insert to a local table that works fine with the trigger, I've deduced that the problem seems to be with the linked server
    Any help or pointers will be greatly appreciated.

    Thank you.

  • November 27, 2018 at 3:52 pm

    #2014731

    joe-584802 - Thursday, November 22, 2018 2:18 AM

    Hi All,

    I have a very simple stored procedure that inserts 2 values to a linked database table.


    ALTER PROCEDURE [dbo].[InsertCustIDRow]
        -- Add the parameters for the stored procedure here
        (@CustID NCHAR(5))
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    INSERT INTO Remsvr.Logistics.dbo.CustomerLogs
        ([CustomerID]
        ,[Action])
      VALUES
        (@CustID
        ,'Inserted')
    END

    When I run the stored procedure from a query window in SSMS, no problem it works without any issues.
    However when the stored procedure is run from an after insert trigger 


    USE [TestDB]
    GO
    /****** Object: Trigger [dbo].[Customer_INSERT]  Script Date: 22/11/2018 09:03:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[Customer_INSERT]
       ON [dbo].[CustomerTest]
    AFTER INSERT
    AS

         EXEC InsertCustIDRow 'Test7'

    I get the following error...


    No row was updated.

    The data in row 4 was not committed.
    Error Source: Net SqlClient Data Provider.
    Error Message: The operation could not be performed because OLE DB provider "SQLNCL11" for linked server "Remsvr" returned message "No transaction is active.".

    Correct the error and retry or press ESC to cancel the change(s).

    If I change the stored procedure to insert to a local table that works fine with the trigger, I've deduced that the problem seems to be with the linked server
    Any help or pointers will be greatly appreciated.

    Thank you.

    I believe you get that error with DTC issues. Check the settings (and firewall) for DTC settings as noted in the following article and see if that helps:
    OLE DB provider SQLNCLI11 for linked server unable to begin distributed transaction

  • November 27, 2018 at 10:47 pm

    #2014748

    Thank you, that fixed it.
    Much appreciated!

    Joe

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply