c# and SMO - Get Message output such as "Table already exists" for logging.

Question

Hi,

I am using SQL Management objects to connect to SQL server. At the moment they contain simple "create table" commands for my example.

I run this code twice on purpose to cause an error for "table already exists".

However my events below are not getting triggered.

Anyone got any ideas, how I can get hold of this message in my code other then changing the ExecutionType to stop on errors causing exceptions ( which I dont want to do, I want to continue)

My Code:

public void executeSomeSQL() {

FileInfo file = new FileInfo(@"\c:\sqlcommands.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
conn.InfoMessage +=new SqlInfoMessageEventHandler(conn_InfoMessage);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.InfoMessage += new SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
server.ConnectionContext.ExecuteNonQuery(script,ExecutionTypes.ContinueOnError);                
MessageBox.Show("All Done");

}

Events:-

 public void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
    {
        textBox3.Text += "1:"+DateTime.Now.ToString();
    }

 public void ConnectionContext_InfoMessage(object sender, SqlInfoMessageEventArgs e)
    {
        textBox3.Text += "2:" + DateTime.Now.ToString();
    }

Many thanks in advance,

J

Answer 1

According to MSDN:

The InfoMessage event occurs when a message with a severity of 10 or less is returned by SQL Server. Messages that have a severity between 11 and 20 raise an error and messages that have a severity over 20 causes the connection to close.

The error severity for a CreateTable on a table that already exists is 16, which bypasses the InfoMessage event.

You might want to wrap your TSQL in a Try...Catch block and use RAISEERROR. A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.

Or, you might want to add a check in your TSQL for the existence of the table and do a Print which will be raised to your InfoMessage event.