Thread: Help Needed on DTC
02-26-02, 04:39 #1Registered User
- Join Date
- Feb 2002
Unanswered: Help Needed on DTC
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
begin distributed transaction
insert into epool9.VDPMASTERTEST.dbo.Project_Master
project_active,project_master_update_flag,project_ master_updated_by, project_master_updated_on)
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
02-26-02, 10:06 #2Registered User
- Join Date
- Dec 2001
- Toronto, Canada
I believe that this error is caused by nested transactions
To prevent your stored procedure from creating nested transactions you can check @@TRANCOUNT to see if a BEGIN TRAN has already been issued.
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