I’ve seen BizTalk log this obscure error message after calling a stored procedure via the WCF-SQL adapter:
System.Data.SqlClient.SqlException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
I traced the error back to the use of DROP TABLE #tempTableName statements, particularly inside CATCH blocks. Removing the DROP TABLE statements from the CATCH blocks surfaced the real, underlying SQL error messages.
It’s bad practice, and simply unnecessary, to explicitly drop temp tables in stored procs. SQL Server caches and reuses temporary objects such as temp tables, so a DROP TABLE statement doesn’t always drop the object anyway. It’s better to let SQL Server manage them by itself.
Here are some references on the topic:
“A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished.” (Reference)
“Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS. None of these things prevent temporary table caching (so it does not matter whether you explicitly drop a temporary table at the end of a procedure or not).” (Reference)
