Code works without transaction but not with transaction?

[Crash]

C#
VS 2003
..Net Framework V1.1 SP1
SQL Server 2000 SP3
Enterprise Library June 2005

I'm working with some code {not of my creation} that performs the
following sequence of actions:

- Open SqlConnection using SQL authentication with UID & PWD in connect
string
- BeginTransaction
- Call stored procedure that updates 1 table {OK}
- Call stored procedure that updates 1 table {FAIL}
- Call stored procedure that updates 1 table
- Call stored procedure that updates 1 table
- Commit
- Dispose

All calls to the stored procedures are coded like this:

SqlHelper.ExecuteNonQuery(transaction,
CommandType.StoredProcedure,
"StoredProcedure",
param, param ...)

SYMPTOMS:

-If I take away the transaction the code works Ok
-If I use the transaction the code always fails on the second stored
procedure call with a "Login failed for user xxx" exception bubbled up
from ADO.NET's ConnectionPool.GetConnection() method
-If I shuffle the order of procedure calls the second call still always
fails
-If I put a breakpoint before the second procedure call the
transaction's connection is Open and the Isolation level is the default
{ReadCommitted}

As the natives were gathering outside my cube with pitchforks and
torches I hacked out the transaction and put something that works back
online. But, what the heck is happening - any ideas?

 

[Nicholas Paldino [.NET/C# MVP]]

Crash,

Well, you obviously have an error with the second stored procedure. You
need to track that down.

As for why everything fails, once there is a fail vote in the
transaction, SQL Server will not let you back it out. You have to redo the
whole thing.

Hope this helps.

 

[Crash]

I switched the order of the procedure calls around - it doesn't matter
what order I call them in the second one always fails. If I remove the
transaction all of the procedure calls succeed...

What disturbs me is that I am in the middle of performing steps in an
active transaction on an open connection - and the exception is
bubbling up from the connection pool???? Why would it try to fetch a
connection from the pool while I'm in the middle of a transaction????

 

[Crash]

I forgot to add:

The transaction is NOT distributed - all stored procedures are
executing in the same database...

 

[Hugo Kornelis]

C#
VS 2003
.Net Framework V1.1 SP1
SQL Server 2000 SP3
Enterprise Library June 2005

I'm working with some code {not of my creation} that performs the
following sequence of actions:

- Open SqlConnection using SQL authentication with UID & PWD in connect
string
- BeginTransaction
- Call stored procedure that updates 1 table {OK}
- Call stored procedure that updates 1 table {FAIL}
- Call stored procedure that updates 1 table
- Call stored procedure that updates 1 table
- Commit
- Dispose

All calls to the stored procedures are coded like this:

SqlHelper.ExecuteNonQuery(transaction,
CommandType.StoredProcedure,
"StoredProcedure",
param, param ...)

SYMPTOMS:

-If I take away the transaction the code works Ok
-If I use the transaction the code always fails on the second stored
procedure call with a "Login failed for user xxx" exception bubbled up
from ADO.NET's ConnectionPool.GetConnection() method
-If I shuffle the order of procedure calls the second call still always
fails
-If I put a breakpoint before the second procedure call the
transaction's connection is Open and the Isolation level is the default
{ReadCommitted}

As the natives were gathering outside my cube with pitchforks and
torches I hacked out the transaction and put something that works back
online. But, what the heck is happening - any ideas?

Hi Crash,

Since it's always the second procedure that fails, regardless of the
order of execution, I suspect that the stored procedures somehow mess up
the transaction nesting level. Maybe they start a transaction that they
don't end, or maybe they end (commit/rollback) the open transaction that
the next procedure expects to be still open?


What happens if you execute a script like the one below from Query
Analyzer:

BEGIN TRANSACTION
EXEC Proc1 param, param, ...
EXEC Proc2 param, param, ...
EXEC Proc3 param, param, ...
EXEC Proc4 param, param, ...
COMMIT TRANSACTION

Do all procedures execute or do you get errors? If the latter, please
copy and paste the error messages.

It might also help to post the code of the stored procedures themselves.

 

[Crash]

- FYI this code is running under ASP, but it is logging into the
database as a specific user. The user that ASP logs in as is not dbo
but does have rights on all of the tables & procedures involved {I
checked}...

- None of the tables involved have any triggers on them {I checked}...

- The entire database has no indexes, no constraints, or any other of
those pesky referrential integrity nuisances {not the way I'd do it
either but I got called in to help on this one}...

- Forgot to mention it earlier but had already tried it - yes - within
query analyzer I can succesfully execute any combination of sproc calls
within a transaction just as you recommended... No errors from Query
analyzer within a transaction no matter what order of execution...

- None of the stored procedures does anything with transactions in and
of themselves - they each insert or update 1 row in a single table per
call... The stored procedures do different things but here's a look at
one of them:

CREATE PROCEDURE dbo.WT_UserInfo_Update
(

@UserID int =Null,
@Password varchar(64) = NULL,
@FirstName varchar(30) = NULL,
@LastName varchar(30) = NULL,
@JobTitle varchar(100) = NULL,
@PhoneNumber varchar(50) = NULL,
@EmailAddress varchar(100) = NULL,
@AdminUserID int = NULL

)
AS
BEGIN
--Update Existing User
Update WT_User
set Password=@Password,
PasswordChangeDate=getdate(),
FirstName=@FirstName,
LastName=@LastName,
JobTitle=@JobTitle,
PhoneNumber=@PhoneNumber,
EmailAddress=@EmailAddress,
ModifiedBy= @AdminUserID
Where UserID=@UserID
IF @@ERROR <> 0 GOTO ExitProcError
return @UserID
END
ExitProcError:
Return(-1)

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[WT_UserInfo_Update] TO [<<same user as used
in connect string>>]
GO

The other procedures do not do anything fancier; 1 deletes from a
{different} table where @UserID=xxx, and a third procedure is called x
times to insert 1 row into the other table per call.

I'm thinking this is more of an ADO issue of some sort, but it is
bizarre. Flawlessly reproducable, but very bizarre...

 

[Hugo Kornelis]

- FYI this code is running under ASP, but it is logging into the
database as a specific user. The user that ASP logs in as is not dbo
but does have rights on all of the tables & procedures involved {I
checked}...

- None of the tables involved have any triggers on them {I checked}...

- The entire database has no indexes, no constraints, or any other of
those pesky referrential integrity nuisances {not the way I'd do it
either but I got called in to help on this one}...

- Forgot to mention it earlier but had already tried it - yes - within
query analyzer I can succesfully execute any combination of sproc calls
within a transaction just as you recommended... No errors from Query
analyzer within a transaction no matter what order of execution...

- None of the stored procedures does anything with transactions in and
of themselves - they each insert or update 1 row in a single table per
call... The stored procedures do different things but here's a look at
one of them:
(snip)

The other procedures do not do anything fancier; 1 deletes from a
{different} table where @UserID=xxx, and a third procedure is called x
times to insert 1 row into the other table per call.

I'm thinking this is more of an ADO issue of some sort, but it is
bizarre. Flawlessly reproducable, but very bizarre...

Hi Crash,

Yeah, I have to agree. Since a logically identical series of statements
from Query Analyzer executes flawlessly, it's probably not a SQL Server
issue.

I have no ADO experience, so I can't help you further. Maybe you should
take this to an ADO group. (I think they'll want to see more of the
front--end code than you posted here, though).