473,378 Members | 1,469 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Code works without transaction but not with transaction?


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?

Feb 1 '06 #1
6 2202
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Crash" <so*********@san.rr.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

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?

Feb 1 '06 #2

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????

Feb 1 '06 #3

I forgot to add:

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

Feb 1 '06 #4
On 1 Feb 2006 14:44:00 -0800, Crash wrote:

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.

--
Hugo Kornelis, SQL Server MVP
Feb 2 '06 #5

- 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...

Feb 3 '06 #6
On 3 Feb 2006 11:50:06 -0800, Crash wrote:

- 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).

--
Hugo Kornelis, SQL Server MVP
Feb 3 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: J Dubal | last post by:
Hello good people, Following works in FC1 (python-2.2.3-7, postgresql-7.3.4-11, kernel-2.4.22-1.2194.nptl, pyPgSQL-2.4) from pyPgSQL import PgSQL conn =...
242
by: James Cameron | last post by:
Hi I'm developing a program and the client is worried about future reuse of the code. Say 5, 10, 15 years down the road. This will be a major factor in selecting the development language. Any...
14
by: David Blickstein | last post by:
I have some XML documents that I want to open in a web browser and be automatically translated to HTML via XSLT. I'm using an xml-stylesheet processing command in a file called "girml.xml". ...
5
by: David Deacon | last post by:
Hi i was given the following advise,below my OriginalQuestion I am a little new to ADOX can you direct me to the following Do i place the code behind a button on a form? Or do i place it in the...
1
by: JBD | last post by:
Hi, I have a button that fires the below sub routine. This works fine if the user clicks the button as they are supposed to, even if they click it repeatedly. However, if you click "refresh" on...
18
by: Joe Fallon | last post by:
I have some complex logic which is fairly simply to build up into a string. I needed a way to Eval this string and return a Boolean result. This code works fine to achieve that goal. My...
2
by: Ben | last post by:
Hi, One ASP.NET transactional page conducts a long transaction in a button click function. I want to display the transaction progress info in label control without refreshing page. It is...
232
by: robert maas, see http://tinyurl.com/uh3t | last post by:
I'm working on examples of programming in several languages, all (except PHP) running under CGI so that I can show both the source files and the actually running of the examples online. The first...
1
by: Jan | last post by:
Hi: I've been tearing my hair out over this one for a few days; did a search of the archives and found similar questions but no real answers. Here goes: This is an application for a retail...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.