Post reply

Stored Procedure which uses a linked server

  • November 15, 2008 at 1:22 am

    #195806

    We are executing a stored Procedure which inserts a record in the other server .Added the other server as linked server and gave its authentication.This is all fine.

    The insert statement is included in begin tran and commit tran like :

    BEGIN TRY

    BEGIN TRAN

    Insert Statement--inserts one row in the other server

    COMMIT TRAN

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN

    END CATCH

    The SP is executing daily ,but for each execution its inserting 2 rows(The insert is happening twice).

    But in Development server ,the same SP is working fine and its inserting only one row.

    What could be the reason?

    Iam executing the SP as: SET XACT_ABORT ON

    EXEC storedproc_name

    in both DEV and Prod servers.Why the result is different in the two servers?

    Let me know if you need any other info.

  • November 15, 2008 at 7:58 am

    #898977

    Hi,

    That's sounds weird. Are you saying that even if you execute the stored procedure from SSMS, two rows are inserted? If so, the only thing I can get off the top of my head is that there is an INSERT trigger on the receiving end (assuming the Prod and the Dev server are not linking to the same server).

    Just for the sake of it; what happens if you strip out the INSERT statement and run only that? And if you didn't already try; if you run the stored procedure "manually" from SSMS?

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

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

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