Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Controlling SQL Transactions with SQL Server 2000 and ADO.NET

Posted on 2011-09-16
10
Medium Priority
?
377 Views
Last Modified: 2012-05-12
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
Comment
Question by:jonatec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 3

Accepted Solution

by:
chandrasekar1 earned 1600 total points
ID: 36548199
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
 

Author Comment

by:jonatec
ID: 36548313
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
 
LVL 25

Expert Comment

by:jogos
ID: 36548480
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Expert Comment

by:chandrasekar1
ID: 36548555
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
 
LVL 25

Expert Comment

by:jogos
ID: 36548664
The sql will crash, but the error returned is perfectly capturable in your C#-code to let it there have an elegant exit.
0
 

Author Comment

by:jonatec
ID: 36548738
Is it better to escalate the problem back to ADO.NET and create a rollback there ?
0
 
LVL 3

Assisted Solution

by:hspoulsen
hspoulsen earned 400 total points
ID: 36549159
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
 

Author Closing Comment

by:jonatec
ID: 36549199
Thanks. Very useful.
0
 
LVL 3

Expert Comment

by:hspoulsen
ID: 36549292
you should not use Approach 2, as

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

0
 

Author Comment

by:jonatec
ID: 36549366
Agreed. I'll use approach 1.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question