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.Begi nTransaction();
cmd = new SqlCommand("tes t", connection);
cmd.CommandType = CommandType.Sto redProcedure;
cmd.ExecuteNonQ uery();

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.con nection is a valid connection or null.

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

"Piotr Strycharz" <Pi************ *@antispam-account.com> wrote in message
news:cp******** *@nemesis.news. tpi.pl...

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.Begi nTransaction();
cmd = new SqlCommand("tes t", connection);
cmd.CommandType = CommandType.Sto redProcedure;
cmd.ExecuteNonQ uery();

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

Regards

[David Browne]

"Piotr Strycharz" <Pi************ *@antispam-account.com> wrote in message
news:cp******** *@nemesis.news. tpi.pl...

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.Begi nTransaction();
cmd = new SqlCommand("tes t", connection);
cmd.CommandType = CommandType.Sto redProcedure;
cmd.ExecuteNonQ uery();

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

new SqlCommand("sel ect @tc = @@trancount)

and bind an output parameter to @tc.

David

[bruce barker]

"Piotr Strycharz" <Pi************ *@antispam-account.com> wrote in message
news:cp******** *@nemesis.news. tpi.pl...
| 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.Begi nTransaction();
| cmd = new SqlCommand("tes t", connection);
| cmd.CommandType = CommandType.Sto redProcedure;
| cmd.ExecuteNonQ uery();
|
| 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("DoC omitt", connection);
cmd.CommandType = CommandType.Sto redProcedure;
bool ok = ((int) cmd.ExecuteScal er()) ==1);

-- bruce (sqlwork.com)


"Piotr Strycharz" <Pi************ *@antispam-account.com> wrote in message
news:cp******** *@nemesis.news. tpi.pl...
| 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.Begi nTransaction();
| cmd = new SqlCommand("tes t", connection);
| cmd.CommandType = CommandType.Sto redProcedure;
| cmd.ExecuteNonQ uery();
|
| After that - how do I know if the transaction is still active?
|
| Regards
|

[Piotr Strycharz]

U¿ytkownik "bruce barker" <no***********@ safeco.com> napisa³ w wiadomo¶ci
news:Ob******** ******@tk2msftn gp13.phx.gbl...

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.