SQL Server Performance

SQL: Distributed Transactions

Discussion in 'General Developer Questions' started by NoFxPT, Apr 17, 2007.

  1. NoFxPT New Member

    Hi folks,

    i got sql server 2000 sp4 and im trying with a linked server to insert the same data in a sql table and also in a as400 (db2) table
    i think the linked server is fine because i can see the tables and also i can insert data in as400 tables but with a simple insert but i created a trigger to

    do the replication of data in sql and as400 and when i try to insert and invoke the trigger it gives me this error:

    Server: Msg 7391, Level 16, State 1, Procedure INSERT_WMCS01F, Line 18
    The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
    [OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver]Enlist with DTC phase failed. 2]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

    This was yesterday... today i used the microsoft ole db provider for db2, also created a datasource using the miscrost Host integration server enabling the data transaction option and with the connection pooling disabled as it says on microsft pages but the problem looks to be almost the same

    Server: Msg 7391, Level 16, State 1, Procedure INSERT_WMCS01F, Line 18
    The operation could not be performed because the OLE DB provider 'DB2OLEDB' was unable to begin a distributed transaction.
    OLE DB error trace [OLE/DB Provider 'DB2OLEDB' ITransactionJoin::JoinTransaction returned 0x80040e14].

    I would appreciate if you could help me...

    Regards,
    Joel
  2. Roji. P. Thomas New Member

  3. satya Moderator

    http://support.microsoft.com/kb/899607&&DI=6243&IG=31eb7d1075d54e15a394def911e0a4c8&POS=1&CM=WPU&CE=1&CS=AWP&SR=1
    http://blog.sqlauthority.com/2007/0...394def911e0a4c8&POS=3&CM=WPU&CE=3&CS=AWP&SR=3

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  4. NoFxPT New Member

    I see, but where exactly should i add those statements? Here is my trigger:

    CREATE TRIGGER INSERT_WMCS01F
    ON WMCS01F
    FOR INSERT
    AS
    BEGIN
    Declare @SESSID varchar(10)
    Declare @PATCADID varchar(50)
    Declare @HOSPID varchar(50)
    Declare @SPECID varchar(50)
    Declare @SALDESC varchar(50)
    SELECT
    @SESSID = SESSID,
    @PATCADID = PATCADID,
    @HOSPID = HOSPID,
    @SPECID = SPECID,
    @SALDESC =SALDESC
    FROM INSERTED
    INSERT INTO DB2_SamsLink.LUSODAT7.WSSAMS.WMCS01F VALUES(@SESSID,@PATCADID,@HOSPID,@SPECID,@SALDESC)
    PRINT 'Test SQL->DB2 OK';
    END
  5. MohammedU New Member

  6. NoFxPT New Member

    Yea it's running... When it's not it says another message...
    Tried almost everything and cant solve it. Changed the dataprovider to Oledb for DB2 as it says in that post the problem is always the same. May be a configuration problem?
  7. satya Moderator

    Have you checked the blog reference above there that should give the relevant information, if not check and ensure the correct and compatible oledb driver is used to connect to DB2.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  8. NoFxPT New Member

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

    Check this, its exactly my problem but
    I did what says there but doesnt work...
  9. NoFxPT New Member

    Yep i checked it and im using the datasource that i created in the tool HIS and the test connection was sucessfull, but the replication still doesnt work
    i will read it again with more careful, thanks
  10. NoFxPT New Member

    Hmm... Is it necessary to create a new publication for replication or configure something like that in the enterprise manager to do this operation?
  11. NoFxPT New Member

    I also used the db2oleb to the linked server and i can see the tables i want in the enterprise manager so i guess the connection is fine but if i query the linked server with a select * from for example i get this error:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'DB2OLEDB' reported an error. The provider did not give any information about the error.
    OLE DB error trace [OLE/DB Provider 'DB2OLEDB' IUnknown:ueryInterface returned 0x80004005: The provider did not give any information about the error.].

    hmm...

    check this:http://support.microsoft.com/kb/277894

    Do you have idead where can i download SNA Server 4.0 Service Pack 4? The microsoft link is broken...
  12. satya Moderator

    I believe you migth have gone thruhttp://www.microsoft.com/technet/archive/sna/downloads/sna4sp4.mspx?mfr=true link. See whether you can get fromhttp://www.microsoft.com/downloads/...7A-4584-4FA0-A908-9FF0C69308EA&displaylang=en SNA MP.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  13. NoFxPT New Member

    thanks for replying but still couldnt find it
    Anyway i guess MS Host integrations server 2006, which one i instaled and got with it a db2 provider, should be enough updated

    I follow exactly what is here:http://msdn2.microsoft.com/en-us/library/aa213281(SQL.80).aspx but still cant query the linked server
    if i use a provider for odbcd i can query normally

    questions for myself that i would like to know the answer:
    provider for odbc supports distributed transactions? If it doesnt, ok, i give up from it
    someone here who already used a linked server to db2 with db2 provider?

Share This Page