January 8, 2009 at 5:00 am
Hi!
I'm working on some multistatement scripts and because of that I'm using transactions. I couldn't find so far a good way of handling errors. I've found an article that says that using dynamic script you can handle errors that normally you can't (http://www.codeproject.com/KB/database/try_catch.aspx), but unfortunetly this approach isn't handle ALL error types and some times somethig wrong happens in the middle of the execution and there is no rollback with the result of corrupted data. I want to be sure that when I run the script on the production DB it will rollback with ANY error and the data will be safe!!!.
Could you help me out to design a safer script? THANKS!!!
This is the script.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
DECLARE @intErrorCode INT
DECLARE @sql NVARCHAR(2000)
BEGIN TRANSACTION
SET @sql = '
ALTER TABLE ua_StoryStation_Version DROP COLUMN Id
CREATE TABLE dbo.Tmp_ua_StoryStation_Version
(
Id int NOT NULL IDENTITY (1, 1),
VersionId int NULL,
StoryId int NOT NULL,
StationId int NOT NULL
) ON [PRIMARY]
SET IDENTITY_INSERT dbo.Tmp_ua_StoryStation_Version OFF
IF EXISTS(SELECT * FROM dbo.ua_StoryStation_Version)
INSERT INTO dbo.Tmp_ua_StoryStation_Version (VersionId, StoryId, StationId)
SELECT VersionId, StoryId, StationId FROM dbo.ua_StoryStation_Version WITH (HOLDLOCK TABLOCKX)
DROP TABLE dbo.ua_StoryStation_Version
EXECUTE sp_rename N''dbo.Tmp_ua_StoryStation_Version'', N''ua_StoryStation_Version'', ''OBJECT''
ALTER TABLE dbo.ua_StoryStation_Version ADD CONSTRAINT
PK_ua_StoryStation_Version PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
'
EXEC sp_executesql @sql
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0)
BEGIN
PRINT @@ERROR
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
January 8, 2009 at 5:41 am
If the errors you hit result in a disconnection from SQL Server, there's nothing that can be done to catch or handle those errors. As for the rest, look up information on TRY/CATCH in the Books Online. It's so much better than the old days under 2000. You can also try this article [/url]for some tips.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2009 at 5:56 am
Thanks Grant Fritchey!
So the only way to be 100% sure that no data will be corrupted because an error during the execution of a script is A GOOD BACKUP BEFORE THE EXECUTION 🙂
January 8, 2009 at 6:04 am
Well, there are no guarantees, but I'd say, it depends on what your script is doing. Your standard, every day, every second, script that's updating a set of tables... it should be covered well by error handling, daily backups and the transaction log backup so you can, if needed, recover to a point in time. Giant import scripts that are inserting huge amounts of data and are run once a month or something... yeah, get a full backup first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 8, 2009 at 9:23 am
besides try / catch blocks, I still use the old school return codes from the stored procedures.
example:
declare @ret int
exec @ret = sp_rename --..... etc
if @ret <> 0
begin
--handle error here
RAISERROR ('la la la' , 16, 1) --maybe even 'with log' if appropriate
--rollback transaction, etc.
end
The return codes for system stored procs can be found in BOL under the section Return Code Values -- I'll not BS you... they usually are only 0 or 1. I always try to include return codes in my own stored procs that denote the nature of the error so I can handle it properly.
Craig Outcalt
January 8, 2009 at 10:48 am
crfenix (1/8/2009)
but unfortunetly this approach isn't handle ALL error types and some times somethig wrong happens in the middle of the execution and there is no rollback with the result of corrupted data. I want to be sure that when I run the script on the production DB it will rollback with ANY error and the data will be safe!!!.
Use TRY.. CATCH. With @@error, it has to be checked after every statement, as not all statements are batch aborting (ie, something may go wrong in the middle of your dynamic and just that statement fails and the next one executes.
With try.. catch, as soon as an error (I believe with a severity > 10) occurs, execution is immediately transferred to the CATCH block
BEGIN TRY
BEGIN TRANSACTION
ALTER TABLE ua_StoryStation_Version DROP COLUMN Id
CREATE TABLE dbo.Tmp_ua_StoryStation_Version (
Id int NOT NULL IDENTITY (1, 1),
VersionId int NULL,
StoryId int NOT NULL,
StationId int NOT NULL
) ON [PRIMARY]
... -- rest of stuff here
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- log error, etc, etc
END CATCH
If you hit an error severe enough to terminate the connection (and those are really severe errors) then the entire lot will roll back if the commit has not been reached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 8:44 am
Thanks you all for your answers! My script will be executes on MANY servers and I think a couple of them are sql server 2000 so Try Catch is not an option 🙂
I think that my script wouldn't cause corrupted data since there is a rollback, but on some errors (I can't remember exactly which one/ones) the rollback was never executed and some changes were commited (those executed before the error)
January 9, 2009 at 8:53 am
The rollback will occur if you trap an error, but you'd need to check for an error after every statement to ensure it gets handled.
If you post the error, we can probably tell why it didn't work.
January 9, 2009 at 8:53 am
crfenix (1/9/2009)
Thanks you all for your answers! My script will be executes on MANY servers and I think a couple of them are sql server 2000 so Try Catch is not an option 🙂
Then you need to check @@Error after every single statement and handle the case if it's not 0. Not all errors will terminate the batch, so using dynamic SQL is not a complete solution.
I wrote a bit about that here - http://sqlinthewild.co.za/index.php/2008/05/20/common-t-sql-mistakes/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 9:52 am
GilaMonster,Steve Jones,SQLBOT,Grant Fritchey: Thanks a LOT!
Now I understood my mistake. I'll fix my script with your suggestions
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply