5

For the database deployments, I call all the db schema changes in a batch file and run them on the target database.

Sometimes we have scripts for altering the views or stored procs. I'm wondering what is the best way to control whether the view/sp has been altered successfully or not and report it via a message in the output log file.

For example below is the pseudo code of what I'd like to implement:

IF 
(
ALTER VIEW vw_abc
.
.
.
.
) Failed THEN 

PRINT 'ALTER view vw_abc Failed'.
ELSE
PRINT 'ALTER view vw_abc Completed Successfully'
END

The output message will be recorded in the script log file which is generated by the batch file that has run the script.

If there any error code that we can check to do that? Any idea?

Thanks.

3

Check sys.objects for the row that has the right value in name and see if the modify_date column is newer than when your script started.

SELECT SYSDATETIME() AS ScriptStart
INTO #scriptstart;
GO
ALTER VIEW ...
GO
ALTER PROC ...
GO
SELECT name
FROM sys.objects
WHERE modify_date > (SELECT ScriptStart FROM #scriptstart);

This will list all the objects that have changed since your script started. You could join to sys.schemas if you care enough... :)

2

Use :on error exit and/or -b. This will cause sqlcmd to exit after the first batch that hits and error, and set the %ERRORLEVEL%:

-b

Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0. If the -V option has been set in addition to -b, sqlcmd will not report an error if the severity level is lower than the values set using -V. Command prompt batch files can test the value of ERRORLEVEL and handle the error appropriately. sqlcmd does not report errors for severity level 10 (informational messages).

If the sqlcmd script contains an incorrect comment, syntax error, or is missing a scripting variable, ERRORLEVEL returned is 1.

Then you can do typical error handling in the batch, by checking the %%ERRORLEVEL%%. In the .sql files you simply run ALTER VIEW.

1

May be this example ,will help you:

ALTER VIEW dbo.vTest
AS

SELECT TOP(10) ProductName FROM [dbo].[TestData]
GO

IF @@ERROR <> 0
    print 'ERR'
ELSE
    print 'ok'
GO



ALTER VIEW dbo.vTest
AS

   SELECT TOP(10) PoductName FROM [dbo].[TestData]
GO

IF @@ERROR <> 0
    print 'ERR'
ELSE
    print 'ok'
GO

And instead of printing the message, we can log the information into a place/table with the name of the object.

IF @@ERROR <> 0
    INSERT INTO Log_Table(L_ObjectName,L_ErrNumber)
    VALUES('vTest',1)
ELSE
    INSERT INTO Log_Table(L_ObjectName,L_ErrNumber)
    VALUES('vTest',0)
GO
1

You could use a DDL trigger to print the output of the object that was affected, or you could use the trigger to automatically log the change in an audit table so that you can view DDL change history.

Here is an example http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.