I have a situation where It′s necessary to update my customer′s database.
So I′ve built a litle executable that executes an updating script using the
osql tool during the installation process.
In the end, the program checks for any entry in a specific table containing
error lgo information.
To prevent users from using the application if any error occurs, I′ve
written some errors checking in the script, as follows:
DECLARE @.errorsave int
IF NOT EXISTS(SELECT nm_version FROM VerInfo where nm_version = 2)
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
ALTER TABLE .... /* first change */
SET @.errorsave = @.@.ERROR
IF @.errorsave <> 0 GOTO CONTROL_ERROR
ALTER TABLE .... /* second change */
SET @.errorsave = @.@.ERROR
IF @.errorsave <> 0 GOTO CONTROL_ERROR
-- update version info block
INSERT INTO [dbo].[VerInfo] (nm_version) VALUES (2)
COMMIT TRANSACTION
END
CONTROL_ERROR:
IF @.@.TRANCOUNT>0
BEGIN
ROLLBACK TRANSACTION
INSERT INTO [dbo].[LogInfo]([TX_Description])
SELECT description FROM [master].[dbo].[sysmessages]
WHERE error = @.errorsave
END
GO
The problem is:
There are some specific situations (like drop column) where, if an error
occurs, the script just stops running and does not perform the error log
saving routine, while, in other situations (like drop table) that saves the
error into "LogInfo" table, as expected.check out this link for a good discussion on SQL error handling:
http://www.sommarskog.se/error-handling-I.html
Friday, March 9, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment