Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    28

    Unanswered: Help Needed on DTC

    Hi,
    I'm having problems while running distributed transactions on two different servers. I
    have two servers 'EPOOL5' and 'EPOOL9' respectively running SQl servers.
    I have linked both the servers using sp_addlinkedserver 'EPOOL5' and sp_addlinkedserver 'EPOOL9' on EPOOL9 and EPOOL5 respectively. I have created a user PCRCCTEST on both the server databases. The databases being VDPMASTER on EPOOL5 and VDPMASTERTEST on EPOOL9. I have given the user PCRCCTEST proper privileges to access the tables in the respective databases.
    I have started DTC on both the servers EPOOL9 and EPOOL5.

    Now when I execute a query say select * from epool9.vdpmastertest.dbo.project_master from EPOOL5, the query is successfully executed and the rows are retrieved. When I insert rows similary, the rows are getting inserted. I am facing a problem when I try to run transactions. i.e I have created a stored procedure 'test' on VDPMASTER database on EPOOL5. 'Project_Master' being the table in EPOOL9 VDPMASTERTEST database. The user PCRCCTEST has privileges to insert data into Project_Master.

    create procedure test
    as
    begin distributed transaction

    insert into epool9.VDPMASTERTEST.dbo.Project_Master
    (project_id,quality_id,project_name,project_client ,start_date,end_date,
    project_active,project_master_update_flag,project_ master_updated_by, project_master_updated_on)
    values (447,'3433','manufacturing','firstbank','02/22/2002','02/23/2003','Y','I',1,getdate())

    if @@error=0
    begin
    commit transaction
    print 'commitTest'
    end
    else
    begin
    print 'rollbackTest'
    rollback transaction
    end

    When I execute this stored procedure from EPOOL5 server, I get the following error.

    Server: Msg 7392, Level 16, State 2, Procedure test, Line 5
    Could not start a transaction for OLE DB provider 'SQLOLEDB'.
    [OLE/DB provider returned message: Only one transaction can be active on this session.]

    I would be grateful if you could help me on this.
    Thanks in advance
    P.C. Vaidyanathan

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I believe that this error is caused by nested transactions

    begin tran
    begin tran

    commit tran
    commit tran

    To prevent your stored procedure from creating nested transactions you can check @@TRANCOUNT to see if a BEGIN TRAN has already been issued.

    Code:
    CREATE PROCEDURE test
    as
    DECLARE @tfTran tinyint
    
    --
    --  Start Transaction
    --
    IF (@@TRANCOUNT = 0) BEGIN
        SET  @tfTran = 1
        BEGIN DISTRIBUTED TRANSACTION
    END
    ELSE
        SET  @tfTran = 0
    
    
        INSERT INTO epool9.VDPMASTERTEST.dbo.Project_Master
        (project_id,quality_id,project_name,
         project_client,start_date,end_date,
        project_active,project_master_update_flag,
        project_master_updated_by, project_master_updated_on)
        values
        (447,'3433','manufacturing','firstbank',
        '02/22/2002','02/23/2003','Y','I',1,getdate())
    
    IF @@error=0 BEGIN
        IF (@tfTran = 1) BEGIN
            COMMIT TRAN
            PRINT 'Commit Test'
        END
    END
    ELSE BEGIN
        IF (@tfTran = 1) BEGIN
            ROLLBACK TRAN
            PRINT 'Rollback Test'
        END
    END

Posting Permissions

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