I have a vb.net app that uses SMO to execute SQL files with the ExecuteNonQuery(SQLFile). The ExecuteNonQuery(SQLFile) is in a TRY CATCH and I catch SQLException, SMOException, and general Exception. When my SQL file has an error, such as a table name that does not exists, it throws a general Exception and the message is "An exception occurred while executing a Transact-SQL statement or batch." I want the message that would be shown on the Messages pane in SSMS "Msg 2812, Level 16, State 62, Line 2. Could not find stored procedure 'ifs_RunHistoryInsertv2x'."

How can I get the Msg from SQL returned to SMO so that I can show is in a messagebox?

OK, looked at the InnerException and seemed to have caught the SQL Msg. Now how do I catch the SQL PRINT statements. My SQL scripts have SET NOCOUNT ON at the top so I am not looking form ROWCOUNT or other info type messages. My SQL Message pane only has PRINT statements and SQL Msg.

How do I capture the PRINT messages?

Catch ex As Exception
    MessageBox.Show(ex.Message)
    returnValue = returnValue + Environment.NewLine + "Exception: " + ex.Message
    Dim ex1 As Exception
    ex1 = ex.InnerException
    Do While ex1 IsNot (Nothing)
        MessageBox.Show(ex1.Message)
        returnValue = returnValue + Environment.NewLine + "Inner Exception: " + ex1.Message
        ex1 = ex1.InnerException
    Loop
End Try

Now how do I catch the SQL PRINT statements. My SQL scripts have SET NOCOUNT ON at the top so I am not looking form ROWCOUNT or other info type messages. My SQL Message pane only has PRINT statements and SQL Msg.

You can handle messages with a severity 10 and lower by adding an InfoMessage event handler like the example below.  Errors with severity 11 and higher can be caught in the exception handler.

    Sub InfoMessageExample()

        Dim s As New Server(".")
        AddHandler s.ConnectionContext.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
        s.ConnectionContext.Connect()
        s.ConnectionContext.ExecuteNonQuery("USE tempdb")

    End Sub

    Private Sub OnInfoMessage(sender As Object, args As SqlInfoMessageEventArgs)
        Console.WriteLine(args.Message)
    End Sub

I added the OnInfoMessage event handler and think that I can make it work. Seems that I need a GO after each of the PRINT statements to get the OnInfoMessage event right when the PRINT is executed. WIthout the GO, if the next statement has an exception (SELECT * FROM MISSPELLEDTABLENAME), the PRINT message will be in the Innertext after the Exception Message. Or maybe I just need to get the Inner Messages in reverse order? Get the Inner most one first? I assume there is a limit to the number of Inner Text Message - any idea what it might be?

• Edited by TheBrenda Thursday, November 13, 2014 6:33 PM clarity

Also, any way to retrieve the Msg, Level, State and Line info?

Msg 208, Level 16, State 1, Line 1

Invalid object name 'missingtabale'.

Also, any way to retrieve the Msg, Level, State and Line info?

Msg 208, Level 16, State 1, Line 1

Invalid object name 'missingtabale'.

The message number, severity, state and line is available via properties of the SqlError and SqlException.  See example below.

    Sub InfoMessageAndExceptionExample()

        Dim s As New Server(".")
        AddHandler s.ConnectionContext.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

        Try
            s.ConnectionContext.Connect()
            s.ConnectionContext.ExecuteNonQuery("SELECT 1 from test")
        Catch efe As ExecutionFailureException
            Console.WriteLine(efe.Message)
            If Not efe.InnerException Is Nothing Then
                If efe.InnerException.GetType Is GetType(SqlException) Then
                    Dim se As SqlException = DirectCast(efe.InnerException, SqlException)
                    Console.WriteLine("Msg {0}, Level {1}, State {2}, Line {3}", se.Number, se.Class, se.State, se.LineNumber)
                Else
                    Console.WriteLine(efe.InnerException.Message);
                End If
            End If
        End Try

    End Sub

    Private Sub OnInfoMessage(sender As Object, args As SqlInfoMessageEventArgs)
        Console.WriteLine(args.Message)

        For Each e As SqlError In args.Errors
            Console.WriteLine("Msg {0}, Level {1}, State {2}, Line {3}", e.Number, e.Class, e.State, e.LineNumber)
        Next e

    End Sub

I added the OnInfoMessage event handler and think that I can make it work. Seems that I need a GO after each of the PRINT statements to get the OnInfoMessage event right when the PRINT is executed. WIthout the GO, if the next statement has an exception (SELECT * FROM MISSPELLEDTABLENAME), the PRINT message will be in the Innertext after the Exception Message. Or maybe I just need to get the Inner Messages in reverse order? Get the Inner most one first? I assume there is a limit to the number of Inner Text Message - any idea what it might be?

Messages generated with PRINT are buffered by default and not sent back to the client immediately.  If you want messages immediately (e.g. progress messages), use RAISERROR...WITH NOWAIT.  For example:

RAISERROR('This message is sent immediately', 0, 0) WITH NOWAIT;

It seemed during my testing that putting a GO after the PRINT made it hit trigger the OnInfoMessage event  right away. Do you think that is correct? Or should I use the RAISERROR? 

Either method is fine.  All messages will be sent upon completion of a batch so that is why the GO batch terminator provides the desired results.  But that is not always possible, such as in a stored procedure or in a loop.  RAISERROR...WITH NOWAIT can be used in those cases.

OK, really dumb question, especially considering that I do not even have my vb.net solution in front of me (it is at work). But I do not know much about distribution of executables. My solution is really simple, it uses SMO to execute a query with results (returns a list of databases), and then I loop through the results (databases) and run a SMO execute no results to run the list of scripts against each of the databases from my query. I compiled my solution at net 3.5 (lowest that I could compile it with) and added the required references to smo, sqlclient, common and whatever else was required. I developed on a machine with SQL 2008 and VS 2010. Once I build the exe, what is required for other servers to run the exe? All servers that run it will have SQL Server installed. But I would assume that they need .net 3.5. but do they need a certain version of any of the references?

This is not a dumb question at all.  You should be covered if the target machine already has the SQL Server Client Tools installed.  If not, you can install SharedManagementObjects.msi, which is available as part of the Feature Pack download for your SQL Server version.  For SQL 2008:

http://www.microsoft.com/en-us/download/details.aspx?id=44277

If all you need to do is run SQL scripts, you could instead run queries using SqlClient instead of SMO to avoid the SMO dependency.  SqlClient is part of the .NET client profile so no separate installation is needed.  However, it would then be your responsibility to read script files and execute the preceding SQL statements whenever a GO command is encountered.  Also, unlike SMO, SqlClient does not support SQLCMD scripts. 

I had looked at using SqlClient, but did not like that the scripts needed to be parsed on the GO statement. I would have to preprocess all the scripts to verify that parsed correctly. (no embedded GO in text).

My app would be executed on servers with SQL 2005 - 2014. And who knows what .NET, os or anything else. Is there anyway I could know in advance if SLQ Server Client Tools was installed? Maybe programmatically, or maybe just be a email saying "look here ... for this .... to verify SQL Server Client Tools is installed".

One method to determine what SMO version is installed is to check the Version value in the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SharedManagementObjects\CurrentVersion

How could I do something like this in Powershell?

I've tried something like this:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null 
 
# Create SMO Server Object
$Server = New-Object ('Microsoft.SQLServer.Management.Smo.Server') "SD-KP-DEVSUP01\KP"
#AddHandler $Server.ConnectionContext.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
$db = $Server.Databases["master"]

try {
    $db.ExecuteWithResults("SELECT name from sys.databases2").Tables[0] 
} 
catch [Exception] {
    $_.Exception|fl -Force
    #[SqlException]$se=$_.Exception.InnerException
}

and I get this Error:

SmoExceptionType : FailedOperationException
Operation        : Execute with results
FailedObject     : [master]
Message          : Execute with results failed for Database 'master'. 
HelpLink         : http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Exec
                   ute+with+results+Database&LinkId=20476
Data             : {HelpLink.ProdName, HelpLink.BaseHelpUrl, HelpLink.LinkId, HelpLink.ProdVer...}
InnerException   : Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Invalid object name 
                   'sys.databases2'.
                      at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
                      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
                      --- End of inner exception stack trace ---
                      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
                      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(StringCollection sqlCommands)
                      at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(StringCollection query)
                      at Microsoft.SqlServer.Management.Smo.Database.ExecuteWithResults(StringCollection sqlCommands)
TargetSite       : System.Data.DataSet ExecuteWithResults(System.Collections.Specialized.StringCollection)
StackTrace       :    at Microsoft.SqlServer.Management.Smo.Database.ExecuteWithResults(StringCollection sqlCommands)
                      at CallSite.Target(Closure , CallSite , Object , String )
Source           : Microsoft.SqlServer.Smo
HResult          : -2146233088

I've looked at all the items in $_ and $Error[0] but can't find any reference to what actual script line failed.

I really want to do output something like this to get more details where the error happened in a big script file.

Console.WriteLine("Msg {0}, Level {1}, State {2}, Line {3}", e.Number, e.Class, e.State, e.LineNumber)