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?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]
- mvp@.spam.guard.caspershouse.com
"Crash" <sourcenexus@.san.rr.com> wrote in message
news:1138833840.836442.96230@.z14g2000cwz.googlegroups.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?
>|||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'|||I forgot to add:
The transaction is NOT distributed - all stored procedures are
executing in the same database...|||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|||- 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...|||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

Clustered Index creation uses extremely much diskspace

Hi,

I have a database in which I have a table that is about 20GB (+/- 9.000.000 records).

This table is created in the filegroup 'SRC_TABLES'.

When I want to create my clustered index on this table - in the same filegroup - it starts to write a lot of data to the harddisk. There is only 18GB free on the harddisk so it fails with the error that he can't create enough free space in the filegroup.

This is the query I use. As you can see it's just a standard clustered index query:

ALTER TABLE dbo.SRC_GRIDFEE_LINE_ITEM_SWITCH ADD CONSTRAINT
PK_SRC_GRIDFEE_LINE_ITEM_SWITCH PRIMARY KEY CLUSTERED
(
GRIDFEE_LINE_ITEM_ID
) ON [SRC_TABLES]
GO

I freed up some space and eventually he created the clustered index, but used more than 20GB of HD-space to do so.
I don't understand why he uses so much diskspace to create that clustered index. If the clustered index is finally created it appears to be only about 4GB.

Thanks in advance.

Thread moved from SSIS Forum to something more appropriate.

In answer to the question, try Books Online, for example "Creating Clustered Indexes"

When a clustered index structure is created, disk space for both the old (source) and new (target) structures is required in their respective files and filegroups. The old structure is not deallocated until the complete transaction commits. Additional temporary disk space for sorting may also be required. For more information, see Determining Index Disk Space Requirements.

|||

Thx for moving. Noticed it too late.

For the clustered index... Been there, done that

Doesn't really provide me with the answers that I need.

Anyone?

|||

!! UPDATE !!

According to the properties windows my table is more than 20GB and my indexes are 35MB !?

I have one index on the table --> the primary key which is clustered.

If I look at what can be found in Books Online (look at DarrenSQL's post), this means that SQL Server needs more than 20GB of extra disk space to create a stupid index of only 35MB?

This doesn't make sence to me...

Can anyone help me out here?