none
How to insert a record into table from one server to another server in Trigger ? RRS feed

  • Question

  • Hi ,
       

       How to insert a record into table from one server to another server in Trigger ?.

    if i insert directly into server2's table from Server1 then its working. but if i insert through Trigger then its not inserting,i'm getting error.


    My Trigger:
    --------------

    Lets assume my Server 1 name is Server1 and Server2 name is Server2

    Server1 :  

        --Creating dummy table
        Create Table tblTest_Server1(col1 int,col2 int)


        CREATE TRIGGER tbl_TR ON tblTest_Server1 FOR INSERT
        AS
        BEGIN
            IF EXISTS(SELECT 'X' FROM Inserted)
            BEGIN
                INSERT INTO [Server2].[dbName].[dbo].[tblTest_Server2](Col1,Col2) SELECT Col1,Col2 FROM     Inserted
            END
        END


    Server 2 :

        Create Table tblTest_Server2(Col1 int,Col2 Int)



    so if i insert into tblTest_Server1 table then trigger should fire where it should insert into server2's tblTest_Server2 table.

        Insert into tblTest_Server1(Col1,Col2) Values(1,11)


    now this simple insert query is executing for 4.59 min, after that i'm getting error like this.

        OLE DB provider "SQLNCLI10" for linked server "Server2" returned message "No transaction is active.".
        Msg 7391, Level 16, State 2, Procedure tbl_TR, Line 6
        The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "Server2" was unable to begin a distributed transaction.


      
    About Linked Servers :
    -----------------------
    here i'm using Linked Server because i'll insert into this table frequently so OPENROWSET,OPENQUERY will not suit to my case.

    Setting :   I did this through GUI in Server 1.
        Linked Server         : Server2
        Server Type        : Checked SQL Server
        In Security tab     : checked 'Be made using this security context' and given server2's userid and password
        In Server Option    : DataAccess=True, RPC=True, RPC Out=True, Enable Promotion of DT for RPC=true, rest of options are False


    Server 1 Config :
        OS : Windows Server 2008 R2
        DB : SQL Server 2008


    Server 2 Config :
        OS : Windows XP
        DB : SQL Server 2008 R2

    Please help me, i'm struck with this prob for last 3 days.  

    Thanks - Ravi


    • Edited by Ravi_P Thursday, December 8, 2011 5:31 AM
    Thursday, November 24, 2011 9:08 AM

Answers

All replies

  • Hi

    Please see this link

    http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp

     

    Hope this helps

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Thursday, November 24, 2011 9:11 AM
  • Thanks for reply.. i already tried with this link..  i did these settings in both servers.  still my prob is not fixed..

    Actually initially i got this error,

    OLE DB provider "SQLNCLI10" for linked server "SQLVillage" returned message "The transaction manager has disabled its support for remote/network transactions.".
    Msg 7391, Level 16, State 2, Line 2
    The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SQLVillage" was unable to begin a distributed transaction

     

    but after done these MSDTC setting,  this error is fixed and then i'm getting this error.

     

    OLE DB provider "SQLNCLI10" for linked server "Server2" returned message "No transaction is active.".
        Msg 7391, Level 16, State 2, Procedure tbl_TR, Line 6
        The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "Server2" was unable to begin a distributed transaction

     

     

    Just check my setting is correct here,

     

    Both Server (Windows Server 2008 R2 and Windows XP ):
    ----------------------------------------------------------------------------

    Network DTC Access                  : Checked
    Allow Remote Clients                 : Checked
    Allow Remote Administration     : Unchecked
    Allow Inbound                           : Checked
    Allow Outbound                         : Checked
    No Authentication Required       : Checked
    Enable XA Transactions              : Unchecked


    Windows XP ( server2 ) :
    --------------------------------

    Enable TIP Transactions        : Unchecked

    Windows Server 2008 R2 ( Server1 ) :
    --------------------------------------

    Enable SNA LU 6.2 Transactions    : Unchecked

    please help me, any changes on these setting will solve my prob ? anything wrong on this setting ?

     


    Thanks - Ravi
    • Edited by Ravi_P Thursday, November 24, 2011 10:01 AM
    Thursday, November 24, 2011 9:56 AM
  • See the below example
    EXECUTE ('
            insert into DB2.dbo.UNIQUESSN(SSN)
            select distinct replace ( ltrim(rtrim(ssn)), ''-'', '''')
            from DB3.dbo.CASE_REGISTRATION_FRM
            where replace(ltrim(rtrim(ssn)), ''-'', '''') NOT IN
                (select distinct SSN from DB2.dbo.UNIQUESSN)
        ') AT [LinkedServer];

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, November 24, 2011 10:07 AM
    Moderator
  • Ravi

    Please see this link

    http://support.microsoft.com/kb/2027550

    http://www.sqlwebpedia.com/content/msdtc-troubleshooting

     

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Thursday, November 24, 2011 10:10 AM
  • Hi.

     

       Thanks for the reply..   Actually server system's firewall is blocking . when i stopped firewall then its working.. Its inserting from one server to another server through Trigger..

     

    but how can i stop firewall blocking of sql linked server. ?

     

    I have posted this question as separate thread.

    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/d3dc66b5-1ea0-4435-aa50-54f1881ba054

     

     

     


    Thanks - Ravi

    Thursday, December 8, 2011 5:00 AM