Automatic rollback inside using statement?

If I start a transaction using the following approach ...

using (SqlTransaction trans = destConn.BeginTransaction())
{

...do some transfers using SqlBulkCopy
}

...will an automatic rollback occur in case of unhandled errors inside the scope of the using statement?

No.If an error occurs,Rollback the transaction in acatch statement to void out any changes and thenrethrow the error so that the application can deal with it accordingly.

http://davidhayden.com/blog/dave/archive/2005/10/14/2515.aspx

|||Is it really necessary to close the connection inside ausing (SqlConnection connection1 =new SqlConnection(connectString1)) block like David Hayden does?|||

They don't do it here:

http://msdn2.microsoft.com/en-us/library/tchktcdk.aspx

Also look at TransactionScopes they're pretty interesting:

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

|||

Let me tell you the internals of transaction it is mathmatically defined to be one operation at a time by the people who created it but SQL Server langauge T-SQL let you create more than one at a time through what we call transaction savepoints you may have seen them in David's blog entry. That is called nested transaction but you must use savepoint or the whole thing will rollback to the first if there is an error. Those savepoints can be rolled back as of service pack 3 of SQL Server 2000 if needed. And you must use the second Using statement before you use them so the system can call dispose for you automatically, that is good practice so resources like transaction is not running when you are through with them. And if you are not hosting the application in your local intranet you cannot use transaction scope because transaction scope performs illegal none atomic transactions and SQL Server does the right thing and promotes the operation to distributed transaction, if you use transaction scope you must call a T-SQL stored procedure doing the correct atomic transaction to finish your transaction block. There is an auto commit mode that is the default unless you change the definition in SQL Server and automatic roll back is if you don't use the savepoint and there is an error in your code. Post again if you still have questions. Try the thread below and read the code and the info in the links in it and you will understand what I have explained here. Hope this helps.

http://forums.asp.net/thread/1284424.aspx

|||

Thanx!

I'll read the thread

automatic or manual ROLLBACK

Hi,
I have a job that executes the TSQL below. Do I need to do the
errorchecking myself (as below) or does the transaction automaticaly
rollback if a statement fails?
Feel free to comment on the script. (Its never too late to learn).
Yours sincerely,
Jo
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @.MYERROR
BEGIN TRANSACTION
INSERT INTO sequencetoolingbak
(
orderid,
partno,
jobno,
deliverydate,
time_of_creation
)
SELECT orderid, partno, jobno, deliverydate, time_of_creation
FROM dbo.sequencetooling AS seq
WHERE deliverydate < DATEADD(d, -14, GETDATE())
AND NOT EXISTS
(
SELECT orderid
FROM dbo.sequencetoolingbak AS bak
WHERE bak.orderid = seq.orderid
)
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM dbo.sequencetooling
WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Jo SegersHi
Relying on default error handling to be the same in future versions of SQL
Server is not good at all as it could change.
You should always handle the error yourself (like in your code) as the
execution will be predictable.
Regards
Mike
"Jo Segers" wrote:

> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers
>|||The transaction will not be automatically rolled back, unless you SET
XACT_ABORT ON first. Some errors will terminate the batch, which means that
your error handler won't be executed though.
For a complete explanation of errorhandling in SQL Server, see the articles
by SQL Server MVP Erland Sommarskog on www.sommarskog.se .
Jacco Schalkwijk
SQL Server MVP
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eSgVzgsAFHA.4008@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Relying on default error handling to be the same in future versions of SQL
> Server is not good at all as it could change.
> You should always handle the error yourself (like in your code) as the
> execution will be predictable.
> Regards
> Mike
OK,
Thanks for your reply.
Jo Segers

Auto Rollback of transaction

Hello,
I had a discuss of SQL Server's auto rollback behaviour with my colleague.
He hasn't convinced me.
We use SQL Server 2000.
What I've noticed is that, inside a stored procedure, after BEGIN TRANS
txTransName
If an error is generated while manipulating table data, then the whole
transaction will roll back by SQL server, and SQL does not process any other
code further down the SP.
However if an error is generated by 'non-table minipulation statement', for
example, do a SELECT 100/0 (division by zero), then SQL does not roll back
the transaction.
The point of this discussion is that, for a while, we've been checking for
errors (IF @.@.ERROR <> 0....do something) right after table minipulation
statements. Now I see that this way of checking for error is actually
pointless, since SQL jumps right out of the SP when it encounters table
minipulation errors anyway.
But I can't be 100% sure about my argument. So is there anyone out there
that can confirm what I am thinking is right or wrong?
Thank you for your time.
ConaxHI
Read this
http://www.sommarskog.se/error-handling-II.html
Regards
R.D
"Conax" wrote:

> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any oth
er
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement', fo
r
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>
>|||Hi Conax,
As you can see from the following sample code in both cases 1) table
manipulation
and 2) non table manipluation, the stored proc does continue and @.@.error
should be checked right after the statement in question.
Please check Books online (BOL) for more information on @.@.error checking.
When in doubt, its a good idea to type a sample test code and see how things
work.
HTH...
set nocount on
go
create table t
(
c1 int not null,
c2 int
)
go
insert t values(1,1)
insert t values(1,2)
insert t values(2,1)
insert t values(2,1)
go
CREATE PROCEDURE dbo.proctblerror
AS
/* you mentioned...
What I've noticed is that, inside a stored procedure, after BEGIN TRANS
txTransName
If an error is generated while manipulating table data, then the whole
transaction will roll back by SQL server, and SQL does not process any other
code further down the SP.
*/
begin tran txTransName
update t set
c1 = null
where c2=1
if (@.@.error = 0)
begin commit tran txTransName print 'commited tbl manip' end
else
begin rollback tran txTransName print 'rolled back tbl manip' end
print 'running rest of tbl manip proc'
select * from t
GO
EXECUTE dbo.proctblerror
GO
CREATE PROCEDURE dbo.procnontblerror
AS
/*
You also mentioned...
However if an error is generated by 'non-table minipulation statement', for
example, do a SELECT 100/0 (division by zero), then SQL does not roll back
the transaction.
*/
begin tran txTransName
select 222
if (@.@.error = 0)
begin commit tran txTransName print 'commited non tbl manip' end
else
begin rollback tran txTransName print 'rolled back non tbl manip' end
print 'running rest of non tbl manip proc'
select * from t
GO
EXECUTE dbo.procnontblerror
GO
drop table t
DROP PROCEDURE dbo.proctblerror
drop proc dbo.procnontblerror
GO
http://zulfiqar.typepad.com
BSEE, MCP
"Conax" wrote:

> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any oth
er
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement', fo
r
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>
>|||Thanks R.D and Z.S, you've been a great help.
Regards
Conax
"Conax" <ConaxLiu@.hotmail.com> wrote in message
news:e%23hlvbCtFHA.1472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any
other
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement',
for
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>