Post reply

While Inserting record through trigger on remote server through linked servers, SQL Server gets hanged.

  • April 12, 2005 at 11:09 pm

    #163238

    While Inserting record through trigger on remote server through linked servers, SQL Server gets hanged.

    In my program, I am creating a trigger on a table for update, insert and delete. When any of this event is happened, the trigger is fired and one row gets inserted in the table which is on the remote server. To connect the table on the remote server, I am using linked servers.

    The triggers gets created successfully, but when any event is occured (update/insert/delete) SQL Server gets hanged.

    The trigger is :

    CREATE TRIGGER TR_TABLE_INSERT ON TABLE_48

    FOR INSERT

    AS

    set xact_abort on

    DECLARE @memberid int

    DECLARE @aliasid int

    SELECT @memberid=fid,@aliasid=faliasid from inserted

    INSERT [remoteserver].[dbname].[dbo].TABLENAME(PID,TABLE_ID,TABLE_NAME,EVENT,MEMBER_ID,COMPOSITE_ID,TIME_STAMP) VALUES(48,2,'TABLE_48','I',@memberid,@aliasid,getdate())

    My SQL Server 2000 version is 8.0.0.760.

    Can anybody help me on this?

  • April 14, 2005 at 12:32 am

    #552600

    there can be tons of reasons why it gets stuck

    - how is your linked server defined

    - problems with the network ?

    - is the second server up and ready to use ?

     

    This is the reason we do not use triggers accessing remote objects !

    (we don't want server1 depending on server2 !)

    We keep triggers a short a possible because they affect any single transaction !

    So we log local and ship batches to a remote server

    or have replication in place.

     

    - Keep in mind a trigger is fired once per tableaction.

       This means that the 'inserted / deleted' can have multiple rows !

       Your trigger will miss some rows in this case !

       INSERT [remoteserver].[dbname].[dbo].TABLENAME

        (PID,TABLE_ID,TABLE_NAME,EVENT,MEMBER_ID,COMPOSITE_ID,TIME_STAMP)

          select 48,2,'TABLE_48','I',fid,faliasid ,getdate()

          from inserted

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • April 14, 2005 at 1:12 am

    #552614

    Hi,

    Thanks for the reply.

    I am adding a linked server using the following commands:

    sp_addlinkedserver sSourceDBServer ,N'SQL Server'"

    sp_addlinkedsrvlogin SourceDBServer,'false', NULL, SourceUserName,SourcePwd

    The remote(linked) server is on the same network, but a different machine. There is no problem of network. We have complete access of both the servers.

    We are not facing above problem on 2 machines, while we are facing it on other 2 machines. So it might be any configuration/administration problem.

     

  • April 14, 2005 at 1:19 am

    #552615

    win2k3 ? MSDTC enabled for remote usage ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • April 14, 2005 at 3:30 am

    #552636

    Hi,

     

    I am using Win2K. MSDTC is on on both the servers

  • June 11, 2009 at 9:47 am

    #1008109

    I have the same problem,I'm sure from linked server configuration

    also i tested remote insert statement out of trigger,it works like sharm but the problem raises when i put it in the trigger

    please help,i spent one day searching solution with no luck

  • June 11, 2009 at 11:52 pm

    #1008401

    Can you give come config info ?

    (both servers/instances)

    - OS ?

    - sqlserver @@version

    - linked server ddl statements/authentication settings

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 7 posts - 1 through 6 (of 6 total)

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