Prev: Partition, sum and....?
Next: Chris Daten Seminar in Mid-May in Scotland
From: Dan on 15 Mar 2010 08:21

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

At step 2, are you looking at the max value of the column with the identity?
If so, this not the way to check the last identity value that was attempted
to be used, as there won't be a row with the value if it was rolled back.

You can use DBCC IDENT to find the current identity value for a table,
whether the latest row has been rolled back or not:
http://msdn.microsoft.com/en-us/library/ms176057.aspx

--
Dan

From: Andy B. on 15 Mar 2010 12:41
Is there a .net version of @@trancount if I use TransactionScope?

[VB]
Dim Query = "..." 'Some sort of insert statement.
try
using Scope as new TransactionScope
using Connection as new SqlConnection("context connection=true")
Dim Command as new SqlCommand(Query, Connection)
Connection.Open()
SqlContext.Pipe.ExecuteAndSend(Command)
end using
Scope.Complete
end using
Catch Ex as Exception
'If we got to this point, the transaction failed for some reason.
'Is there something I can use here in place of @@trancount=0?

'Test transaction state.
'If failed, build an error result returning -1 and Ex.Message.
'Once built, send it to the client with SqlContext.Pipe.Send()
end catch

I have a complete working version of the Stored procedure if you want to see
what I came up with so far. The only thing it can't do at this point is
figure out if the transaction did rollback and why exactly did it do that? I
thought about using TransactionInformation object to try and figure this
out, but I don't know if it will work with the TransactionScope object
either.
"Tony Rogerson" <tonyrogerson(a)torver.net> wrote in message
news:1F8BE1B4-5183-467A-B644-5E7F0B49BDAF(a)microsoft.com...
> 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.
>>


First  |  Prev  | 
Pages: 1 2
Prev: Partition, sum and....?
Next: Chris Daten Seminar in Mid-May in Scotland