SqlTransaction issue

[Piotr Strycharz]

Hi,

The question is about how to check if the transaction is still active. Let's
say, that I am using a 3rd party stored procedure, that cannot be changed.
Sample is (Of course, the real one is much more complicated):
CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN

Now, I'm executing code:

tran = connection.BeginTransaction();
cmd = new SqlCommand("test", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();

After that - how do I know if the transaction is still active?

Regards

 

[Sahil Malik]

For this same purpose, The Microsoft Data Access block checks to see if the
transaction.connection is a valid connection or null.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[David Browne]

Hi,

The question is about how to check if the transaction is still active.
Let's
say, that I am using a 3rd party stored procedure, that cannot be changed.
Sample is (Of course, the real one is much more complicated):
CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN

Now, I'm executing code:

tran = connection.BeginTransaction();
cmd = new SqlCommand("test", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();

You can examine the @@trancount. If it is > 0 then the transaction is
active. Otherwise the transaction has been rolled back.

new SqlCommand("select @tc = @@trancount)

and bind an output parameter to @tc.

David

 

[bruce barker]

| Hi,
|
| The question is about how to check if the transaction is still active.
Let's
| say, that I am using a 3rd party stored procedure, that cannot be changed.
| Sample is (Of course, the real one is much more complicated):
| CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN
|
| Now, I'm executing code:
|
| tran = connection.BeginTransaction();
| cmd = new SqlCommand("test", connection);
| cmd.CommandType = CommandType.StoredProcedure;
| cmd.ExecuteNonQuery();
|
| After that - how do I know if the transaction is still active?
|
| Regards
|

 

[bruce barker]

the commit will fail if no pending transaction exits - this will be your
fallback.

procs generally test @@trancount to avoid throwing an error on rollback when
there is no nested transction. you need to look to see how the proc is
returning failure - return value, raiserror, or paramter.

you are going to use multiple batches be sure to use the same connection,
and don't close it. at anytime you can select @@trancount to see a
transaction is active. you could also use the follow sp to do the commit.

create proc DoCommit as
set nocount on
if @@trancount > 0
begin
commit tran
select 1 as CommitResult
end
return 0 as CommitResult

then use

cmd = new SqlCommand("DoComitt", connection);
cmd.CommandType = CommandType.StoredProcedure;
bool ok = ((int) cmd.ExecuteScaler()) ==1);

-- bruce (sqlwork.com)




| Hi,
|
| The question is about how to check if the transaction is still active.
Let's
| say, that I am using a 3rd party stored procedure, that cannot be changed.
| Sample is (Of course, the real one is much more complicated):
| CREATE PROC test AS IF @@trancount > 0 ROLBACK TRAN
|
| Now, I'm executing code:
|
| tran = connection.BeginTransaction();
| cmd = new SqlCommand("test", connection);
| cmd.CommandType = CommandType.StoredProcedure;
| cmd.ExecuteNonQuery();
|
| After that - how do I know if the transaction is still active?
|
| Regards
|

 

[Piotr Strycharz]

the commit will fail if no pending transaction exits - this will be your
fallback.

Yes ! And it fails, indeed. However, my code is not aware of the ended
transaction, so it executes consecutive commands **without** transaction
(although transaction variable is not null).

Seems, that the @@trancount sql variable is the one solution.

Regards.