First, thank you for taking the time to read this issue. I've spent several days researching this problem, with little to show for it.

Now onto the issue..

I wasn't sure if this was more of a DB (MS SQL 2000) type question, or an ASP (Win2k Adv. Server) type question, but since the problem manifests itself in ASP only, I'll post here (should the moderator see fit to migrate this to the DB forums, my apologies)

I've been creating an admin function for an application. This admin function updates a primary DB table with data from the user. That update requires updates to several other secondary support tables. Since these secondary updates are autonomous and require no other input from the user, I decided to build a trigger in the DB to execute on updates to the primary table.

This trigger functions properly when an update to the primary table is executed from a Query Analyzer window, but when this trigger is executed from the ASP, I receive the error (this is the exact msg):

"Cannot create new connection because in manual or distributed transaction mode."

The process that executes in ASP takes the following steps

1) Form submit with user data
2) Data preprocessed to prepare for transaction
3) Begin DB transaction
4) Update Primary table with preprocessed data
4a) Trigger Fires and updates secondary tables
4b) If update fails rollback trans, end processing with error
5) Inserts into a third table
5a) If insert fails rollback trans, end processing with error
6) Deletes from a fourth table
6a) If delete fails rollback trans, end processing with error
7) If all succeed, commit trans
8) End processing

The error occurs at step 5, the insert after the trigger. My research has so far led me to believe that the insert at step 5 is stumbling over the continued execution of the trigger in step 4a. Some solutions I've come across have said "Close the connection, then reopen it", which I cannot do since this all has to rollback should any error occur. Another solution was to use server-side recordsets (Dynamic or KeySet) instead of client-side recordsets (Forward Only, R/O), but in ASP all recordsets are server-side, so using Dynamic or KeySet did not solve the problem (This was more of a VB solution to the same problem).

Now, I have a work around. I've moved step 4 (including 4a and 4b) after 6a (Making the trigger the last thing to execute), but this seems to be a kludge. There should be some way to make ASP wait for the completed execution of the trigger before continuing on to the rest of the processes.

If you need more detail on the actual code, I'd be more then happy to post it.

Any ideas?

Thanks again for your assistance!