What's the difference between an SQL transaction at the stored procedure level and one at the SqlConnection level?

Question

Say a stored procedure on MSSQL Server uses an SQL transaction using `BEGIN TRANSACTION/COMMIT TRANSACTION`, how does that differ from beginning and commiting one using ADO.NET via `SqlConnection.BeginTransaction()`?

Accepted Answer

For ADO.NET, it's no difference. It's implicitly stated in MSDN where for SqlTransaction object the Commit method is said to "fail if the transaction has already been rolled back on the server."

Also, SQL Server Profiler shows "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRAN" as soon as you execute .BeginTransaction on a connection.

For ADO (not .NET), however, it's not. That used to allow nice scenarios with, effectively, nested transactions (server trans were nested inside client ones). Despite I have used that a lot, I can't define exactly what a "client transaction" was in that case.

What's the difference between an SQL transaction at the stored procedure level and one at the SqlConnection level?

Solution

Problem