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,
SubhaCrosspost

http://www.dbforums.com/t970872.html (http://www.dbforums.com/t970872.html)|||I could not see any reply posted by enigma??????

Originally posted by subkrish
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,
Subha