I would love the developers to change the code so they use the @@Identity. This is a much better solution than using NOLOCK. Unforetunatly, for this move to SQL 2K, management wanted to not make any code changes. I ran the trace and found that the transaction isolation level is not being explicitly set. So it must be using the default. I also had the connection usage wrong. It actually is like this: Begin Tran, Insert into Table1 (on connection1), Select max() from table1 (on connection1) then insert into table2 (on connection2). The second connection is not intentional. The ado commands for these three steps are: 1) gadoConn.execute (Insert into Table1), 2) rsTemp.Open (select max()), gadoConn, adOpenForwardOnly,,adCmdText and 3) gadoConn.execute (Insert into Table2)

We are guessing that the rsTemp.Open statement is keeping the connection tied up so when the next gadoConn.execute fires it has no choice but to use a new connection. It turns out that the trigger is an Update trigger and of no consequence to this problem. We did discover two ways to make this problem go away. One was to change the rsTemp.Open statement to gadoCommand.execute which eliminates the second connection and the other was to drop the Foreign Key in Table2 pointing to Table1. FK's were added to this table and a number of others as part of this migration and when I removed the one on Table2, the 2nd insert statement was able to complete. Eventhough I personnally demonstrated that removing the FK resolved this isssue, I really have no idea why their presence would cause the hang. So. for the difference between SQL 7 and SQL 2k, the answer is the FK's.

Mark Hottinger