Help on DTC

[vaidyanathanpc]

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