Error running stored procedure after insert trigger

• joe-584802

  SSC Eights!

  Points: 949

  More actions

  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.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  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

• joe-584802

  SSC Eights!

  Points: 949

  More actions

  November 27, 2018 at 10:47 pm

  #2014748

  Thank you, that fixed it.
  Much appreciated!

  Joe