I have a question. MS documenatation on XACT_STATE shows them checking for XACT_STATE = 1 and doing a commit in the catch block. In what scenario would XACT_STATE be equal...

Here is another possibility. Let me say before hand that Jeff Moden's solution is better than this for the specific task you asked for; (on my laptop, my solution is...

Kavita,

I know this is not a perfect solution, but it might work for you on SQL Server 2000. I am assuming your hierarchies are at most 7 levels deep. (If...

This should work if I am understanding your problem correctly. Please test it with different test cases.

I have assumed that there is a identity row id without gaps for each...