C # and SMO - Get a message like "Table already exists" for logging

Question

I am using SQL Management objects to connect to a SQL server. They currently contain simple "create table" commands for my example.

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

However, my events below do not fire.

Does anyone have any idea how I can get this message in my code and then modify the ExecutionType to stop the errors causing the exceptions (which I don't want to do, I want to continue)

My code is:

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"); } 

Developments: -

 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(); } 

Answer 1

According to MSDN :

An InfoMessage event occurs when a message with a severity of 10 or less is returned by SQL Server. Messages that have a severity of between 11 and 20 raise an error message and a severity of more than 20 reasons for connecting to a close.

The error rate for CreateTable in an existing table is 16, which bypasses the InfoMessage event.

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

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

Answer 2

I am not a programmer (DBA), so I'm not sure. I guess the next cam will be useful

conn.FireInfoMessageEventOnUserErrors = true;