Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Unanswered: Triggers and linked servers

    I've been working on this for a couple of days now, trying different scenarious.

    Problem:
    I created a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to collect information and insert to a linked server server2.dbs2.owner.table2. When I run with the trigger active, SQL Analyzer just hangs in there "Executing Query Batch ..." indefinately. When I don't create the trigger, but run all parts manually from SQL Analyzer, it works fine.

    What did I try:
    1. create a 'FOR INSERT' trigger on server1.dbs1.owner.table1 to insert into server1.dbs1.owner.table2. This worked fine.
    2. create a stored proc to execute within the trigger on server1.dbs1.owner.table1 to insert to server2.dbs2.owner.table2. This just hangs in there "Executing Query Batch ..." indefinately.

    What now?
    I have a suspicion that something is not working correctly with the triggers and the linked servers. Has anyone encountered a similar problem and what did you do to overcome this? I greatly appreciate all responses and suggestions. Thanks all.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    This is working for me:

    create trigger tr_Table1 on Table1
    for insert,delete,update
    as
    insert MSSQL200.test_new33.dbo.Table1
    select * from inserted

    delete MSSQL200.test_new33.dbo.Table1
    where id in(select id from deleted)
    go
    select * from Table1
    select * from MSSQL200.test_new33.dbo.Table1

    set XACT_ABORT on
    insert Table1
    select 1,'a'

    delete Table1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •