Difference of ADO.Net's Transaction connecting to SQL Server 2000 and SQL Server 2008

Question

hi, I have some C#/ado.net code which acts differently with SQL Server 2000 and SQL Server 2008.

SqlConnection con = new SqlConnection("connecstionstring");
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MySp";
con.Open();
SqlTransaction trans = con.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();// this step will fail with sql server 2008 but success with 2000
con.Close();

and the sp code as:

ALTER PROCEDURE MySp
AS
BEGIN
    COMMIT --this match the outside 'begin trans' in c# code
    do some thing...
    BEGIN TRANSACTION -- this match the outside 'commit' in c# code
END

When connecting to SQL Server 2008, the C# code will fail at 'trans.Commit()', and the exception says: the transaction has been commited, can not use again.

But connecting to SQL Server 2000, it will succeed. Please tell me why?

Answer 1

I am not sure, but I suppose the sp commits the transaction in sql server 2008, and it can not be commited twice.