Interesting SqlException

Working with a ReST API that uses Linq to SQL for a DAL.

We have a method that calls a stored procedure that does various SQL statements and does it in a transaction that it either commits or rolls back.

If we called this method 5 times in a row, it would “hang” the api for subsequent calls that did multiple queries. Any call that traversed a relationship and queried the child table would throw this SQL exception:

The server failed to resume the transaction. Desc:#########

The proc that was called returns a table of errors (if any). Our API was never consuming this table.

Thanks to this blog entry, I was enlightened into the need to consume that return table.

Once I consumed the return table, the connections stopped being “stuck” with a transaction context.