CODEWITHSUNDEEP
c#sql-serverado.netsmo
Mani5556
Mani5556

Reputation: 403

Get error message from ExecuteNonQuery statement with SMO in C#

I'm trying to execute a script and catch errors (if any) and write the output somewhere.

I have this:

...
    SqlConnection connection = new SqlConnection(connString);
    Server server = new Server(new ServerConnection(connection));
    server.ConnectionContext.ExecuteNonQuery(script);
...

When there is some problem in the SQL I am having trouble getting the actual problem of the query.

I have tried

a.) Catching ExecutionFailureException and handling it - the error itself has no information related to the SQL problem and

b.) I've tried cycling through the server.ReadErrorLog() but this seems to be related to the error log of the server connection, not so much the context of the SQL being executed, and

b.) I've tried adding an InfoHandler event handler on the ConnectionContext which never seems to fire.

How can I get a detailed error message? (i.e. the error that I would see if I were to run the SQL in management studio would be perfect, if possible)

Upvotes: 2

Views: 2740

Answers (1)

Mani5556
Mani5556

Reputation: 403

I just figured it out, instead of looking at the ex.Message which returns a vague

An exception occurred while executing a Transact-SQL statement or batch.

I simply had to refer to the ex.GetBaseException().Message of the ExecutionFailureException and I received more meaningful information:

Invalid object name 'FakeTable' (or whatever the error is)

Code: 

...
try
{
    SqlConnection connection = new SqlConnection(connString);
    Server server = new Server(new ServerConnection(connection));
    server.ConnectionContext.ExecuteNonQuery(script);
} catch (ExecutionFailureException ex) {
    MessageBox.Show("Error: " + ex.GetBaseException().Message); 
}

Upvotes: 3

Related Questions