Please provide me with a solution

I am developer who codes for both front end and back end.

HEre is the situation. I have two databases sqlnts1 and sqlnts2. I need to update certain tables in sqlnts1 and then certain tables in sqlnts2 and then back to the sqlnts1 to update few more tables. This is the program logic.

When I tried executing the stored procedure written in sql server 2000 I am provided with the following message

"[OLE/DB provider returned message: Cannot start more transactions on this session.]"

To set this problem correct the only option that I have is to set xact_abort on. But, setting this option on I cannot handle the errors in code. That is this option lets the server handle the system errors.

I want to handle the errors using @.@.error programatically.

Also when I try to commit the transaction that is started before updating sqlnts1 at the end of the stored procedure it provides me with the following error.

"No transaction or savepoint of that name was found".

To work around this problem I used set xact_abort on before making updates to sqlnts2 and set it back to off after finishing updates to sqlnts2.

This way it works, but am sure given that there occurs an error in updating sqlnts2 server tables - its gonna create problems. I want to handle them by myself in the code.

Please help.

Here is the program flow. I tried beginning the distributed transaction too. Case is also checked and begin and end tran are ensured to have the same case.

Begin distributed transaction abc / begin tran abc

Update tables in sqlnts1

set xact abort on

update tables in sqlnts2

set xact abort off

Update tables in sqlnts1

commit tran / rollback tran abc

Thanks,
SubhaAre these 2 DBs on the same server or different servers?|||The sqlnts1 and 2 are two servers.

I would look forward ot hear from you.
Thanks,
Subha

Originally posted by sbaru
Are these 2 DBs on the same server or different servers?|||What is the level of nested transactions you are going into ?
find max value of @.trancount at which the error
"[OLE/DB provider returned message: Cannot start more transactions on this session.]"
occurs|||I have only one transaction on. I don't open multiple transactions.
@.trancount is only 1

Originally posted by Enigma
What is the level of nested transactions you are going into ?
find max value of @.trancount at which the error
"[OLE/DB provider returned message: Cannot start more transactions on this session.]"
occurs