Prev: Partition, sum and....?
Next: Chris Daten Seminar in Mid-May in Scotland
From: Andy B. on 13 Mar 2010 15:32
I have a CLR stored procedure that uses a transaction to insert a row into a
table. I need to tell somehow by looking at my sample data whether or not
the rollback has actually happened. The only thing I have to go on is the
HeadlineID int identity(1,1) not null column. Is this possible?

1. Run the stored procedure with sample data forcing it to succeed
2. Look at the data in the table itself. The current identity is set to 28.
3. Run the stored procedure again forcing it to fail. This is done by a
unique key column [HeadlineTitle].
4. Look at the table again to make sure this run failed. It did. identity
still set to 28.
5. Run stored procedure again forcing it to succeed.
6. Look at table data again. There is a new row, but its identity is set to
30.

I'm confused. Does the identity columns rollback as well? or do they
continue counting even though everything else has been rooled back? Just
making sure before I consider my code broken.


From: Martin POON on 13 Mar 2010 16:11
The IDENTITY value will *not* be rolled back.
The current identity value for the table will still be incremented even
after an INSERT statement fails.


"Andy B." <a_borka(a)sbcglobal.net> д����Ϣ
news:uPDi9wuwKHA.4532(a)TK2MSFTNGP05.phx.gbl...
> I have a CLR stored procedure that uses a transaction to insert a row into
> a table. I need to tell somehow by looking at my sample data whether or
> not the rollback has actually happened. The only thing I have to go on is
> the HeadlineID int identity(1,1) not null column. Is this possible?
>
> 1. Run the stored procedure with sample data forcing it to succeed
> 2. Look at the data in the table itself. The current identity is set to
> 28.
> 3. Run the stored procedure again forcing it to fail. This is done by a
> unique key column [HeadlineTitle].
> 4. Look at the table again to make sure this run failed. It did. identity
> still set to 28.
> 5. Run stored procedure again forcing it to succeed.
> 6. Look at table data again. There is a new row, but its identity is set
> to 30.
>
> I'm confused. Does the identity columns rollback as well? or do they
> continue counting even though everything else has been rooled back? Just
> making sure before I consider my code broken.
>
From: Erland Sommarskog on 13 Mar 2010 18:06
Andy B. (a_borka(a)sbcglobal.net) writes:
> I have a CLR stored procedure that uses a transaction to insert a row
> into a table. I need to tell somehow by looking at my sample data
> whether or not the rollback has actually happened. The only thing I have
> to go on is the HeadlineID int identity(1,1) not null column. Is this
> possible?

If all you have is an IDENTITY column, you will of course have difficulties
to verify your data. Just don't tell me that all other columns are nullable.

> I'm confused. Does the identity columns rollback as well? or do they
> continue counting even though everything else has been rooled back? Just
> making sure before I consider my code broken.

The whold point with IDENTITY is exactly that: they are never rolled back.
This permits parallel processes to insert data with blocking each other.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Andy B. on 13 Mar 2010 20:04

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D3B11585BB2Yazorman(a)127.0.0.1...
> Andy B. (a_borka(a)sbcglobal.net) writes:
>> I have a CLR stored procedure that uses a transaction to insert a row
>> into a table. I need to tell somehow by looking at my sample data
>> whether or not the rollback has actually happened. The only thing I have
>> to go on is the HeadlineID int identity(1,1) not null column. Is this
>> possible?
>
> If all you have is an IDENTITY column, you will of course have
> difficulties
> to verify your data. Just don't tell me that all other columns are
> nullable.

No, they're not. In fact, all of the columns in the table except 1 have
nullable=false. Guess I typed the question wrong. Either way, considering
that all columns except 1 are nullable=false, how do you test rollbacks? The
stored procedure tries to insert data for 5 columns, 1 is auto generated and
a trigger will update/insert the last one.

>> I'm confused. Does the identity columns rollback as well? or do they
>> continue counting even though everything else has been rooled back? Just
>> making sure before I consider my code broken.
>
> The whold point with IDENTITY is exactly that: they are never rolled back.
> This permits parallel processes to insert data with blocking each other.
>

Ok. at least the code isn't broken then.


From: Tony Rogerson on 14 Mar 2010 04:14
Hi Andy,

Ignore IDENTITY its a read heading in your instance, you shouldn't be
looking at the data to see if an insert is successful - you should be error
checking begin try/catch logic.

You can pick up errors in your program.

You can check @@trancount for the number of open transactions and check its
not 0 which would indicate it got rolled back.

Anyway - why is it not possible to just use the .NET error handling?

Many thanks,
Tony.

"Andy B." <a_borka(a)sbcglobal.net> wrote in message
news:uPDi9wuwKHA.4532(a)TK2MSFTNGP05.phx.gbl...
> I have a CLR stored procedure that uses a transaction to insert a row into
> a table. I need to tell somehow by looking at my sample data whether or
> not the rollback has actually happened. The only thing I have to go on is
> the HeadlineID int identity(1,1) not null column. Is this possible?
>
> 1. Run the stored procedure with sample data forcing it to succeed
> 2. Look at the data in the table itself. The current identity is set to
> 28.
> 3. Run the stored procedure again forcing it to fail. This is done by a
> unique key column [HeadlineTitle].
> 4. Look at the table again to make sure this run failed. It did. identity
> still set to 28.
> 5. Run stored procedure again forcing it to succeed.
> 6. Look at table data again. There is a new row, but its identity is set
> to 30.
>
> I'm confused. Does the identity columns rollback as well? or do they
> continue counting even though everything else has been rooled back? Just
> making sure before I consider my code broken.
>
 |  Next  |  Last
Pages: 1 2
Prev: Partition, sum and....?
Next: Chris Daten Seminar in Mid-May in Scotland