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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
From SQL2005 you have more control. But 'accidentally' renamed or deleted column is something
it will crash, if you want to avoid this situation, before doing the DML operation, you can check in the “information_schema.column
The sql will crash, but the error returned is perfectly capturable in your C#-code to let it there have an elegant exit.
ASKER
Is it better to escalate the problem back to ADO.NET and create a rollback there ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. Very useful.
you should not use Approach 2, as
SQL Server sets @@error to 0 if the statement was successful
SQL Server sets @@error to 0 if the statement was successful
ASKER
Agreed. I'll use approach 1.
ASKER
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 ?