SQL Management Object, Continue On Error yet log message

Question

I'm currently using SMO to help automate the patching and/or upgrading of customer databases that are in production. I'm using the following code

Public Sub RunScript(ByVal db As Database, ByVal scriptInfo As FileInfo)
    If scriptInfo.Exists Then

        RaiseEvent LogMessage("Executing Indicated Script: " & scriptInfo.Name)
        Using sr As New StreamReader(scriptInfo.FullName)
            Dim script As String = sr.ReadToEnd
            Try
                db.ExecuteNonQuery(script)
            Catch ex As Exception
                RaiseEvent LogMessage("ERROR: " & ex.Message.ToString)
                db.ExecuteNonQuery(script,
                                Microsoft.SqlServer.Management.Common.ExecutionTypes.ContinueOnError)
            End Try
        End Using

    End If
End Sub

What I'm doing is logging the SQLConnection messages that are coming off of the SQLConneciton that I am using with the Database part that give me fairly simplistic messages about what is happening. Am I'm also Logging myself various messages through the LogMessage(string) event that I have created.

What I would really like to be able to do is execute the scripts so that the continue if there is an error, but still log that there is an error without having to do it the way I am now.

The reason is some of the errors that break the transactions are trivial and it's okay that they are happening. Like trying to create an index when one exists or what-have-you.

Is there any way to both continue script execution on error but still log the error message?

*Edit * A Clarification for what the two objects coming in are

The scriptInfo is a FileInfo object that tells the StreamReader where to load the sql script from.

The db is a SMO Database object that is associated with a SQLClient.Connection that allows me to execute the script on the database that is associated with db

The error logging is happening on the SqlClient.SqlConnections OnInfo Event, or from the LogMessage event

Answer 1

What you posted is just your execution call for whatever 'scriptinfo' has in it.

What is the text of 'scriptinfo'? That's where you need to look / build-in graceful handling for failed CRUD / transactions / etc..

If you can post the 'scriptinfo', maybe I can give you some ideas.

Answer 2

Assuming you are using SQL Server 2005 or above, you could use distinct TRY/CATCH blocks for every statement in the script. Here is an example:

DECLARE @errorLog TABLE (
  row_id INT NOT NULL IDENTITY(1,1), 
  error_msg NVARCHAR(4000)
) 

/** Create the table **/ 
BEGIN TRY 
  CREATE TABLE t1 (RowID INT NOT NULL) 
END TRY 

BEGIN CATCH 
  INSERT @errorLog (error_msg) 
  SELECT ERROR_MESSAGE()
END CATCH 

/** Create the table again - will raise an error message **/ 
BEGIN TRY 
  CREATE TABLE t1 (RowID INT NOT NULL) 
END TRY 

BEGIN CATCH 
  INSERT @errorLog (error_msg) 
  SELECT ERROR_MESSAGE()
END CATCH 

/** Create the index **/ 
BEGIN TRY 
  CREATE INDEX IX_t1 ON t1 (RowID) 
END TRY 

BEGIN CATCH 
  INSERT @errorLog (error_msg)
  SELECT ERROR_MESSAGE()
END CATCH 

/** Return the errors **/
IF EXISTS (SELECT * FROM @errorLog) 
  SELECT error_msg FROM @errorLog

/** Confirm it worked **/ 
EXEC sp_help 't1' 

This script will continue processing after failures. If errors exist, they will be returned in a resultset. So you would need to alter your original code, but I think you might have to anyway in order to accomodate multiple errors within one script.

On the flip side, things like duplicate index errors could easily be handled in the script itself (i.e. check to see if the object already exists). And since this solution requires an extensive amount of changes to the original script, that might be a preferable choice.