Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Controlling SQL Transactions with SQL Server 2000 and ADO.NET

Hi

I have to work on a system that has a SQL Server 2000 database. I am using C# and ADO.NET to access the database. I now have an update to do with 4 tables. Now with SQL Server 2008 I can use a try.. catch block and rollback any failed updates. But given the technology I'm using can someone suggest the most sensible way to begin/rollback/commit a transaction with SQL Server 2000 ?

Many thanks..
0
jonatec
Asked:
jonatec
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
chandrasekar1Commented:
we can catch the error through @@ERROR, while exceuting queries, if there was any error, @@ERROR will set not equal to “0”
below I given 2 approaches using with GOTO and without GOTO.  

Approach 1:

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    IF (@@ERROR <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    IF (@@ERROR <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@@ERROR <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Open in new window


In above approach, we need to write “GOTO” statement after each DML operation, if any error occurs, then it will Rollback the transaction.

Approach 2:

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

IF (@@ERROR = 0) 
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
IF (@@ERROR <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Open in new window

0
 
jonatecAuthor Commented:
Thanks for that.

Question I have is; will SQL Server 2000 control an error caused when a table or column has been accidentally renamed or a column deleted, or will it just crash out ?
0
 
jogosCommented:
It will give a severe error, not controlable just stops , rollback and gives error like 'unkown ...'  
From SQL2005 you have more control.  But 'accidentally' renamed or deleted column is something
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
chandrasekar1Commented:
it will crash, if you want to avoid this situation, before doing the DML operation, you can check in the “information_schema.columns” view and then you can proceed it.
0
 
jogosCommented:
The sql will crash, but the error returned is perfectly capturable in your C#-code to let it there have an elegant exit.
0
 
jonatecAuthor Commented:
Is it better to escalate the problem back to ADO.NET and create a rollback there ?
0
 
hspoulsenCommented:
You should take a look at www.sommarskog.se, in particular http://www.sommarskog.se/error-handling-II.html
that describes what to do on SQL Server 2000.

The solution by chandrasekar1 is fine, but you will not be able to see the @@error number, because "After each statement, SQL Server sets @@error to 0 if the statement was successful" (quote from link above)

Best regards,
Henrik Staun Poulsen
Stovi Software
0
 
jonatecAuthor Commented:
Thanks. Very useful.
0
 
hspoulsenCommented:
you should not use Approach 2, as

SQL Server sets @@error to 0 if the statement was successful

0
 
jonatecAuthor Commented:
Agreed. I'll use approach 1.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now