How to get return value from Sql Server system message?

Question

I am trying to verify the backup I just made in C # using the command against SQL Server Express

string _commandText = string.Format("RESTORE VERIFYONLY FROM DISK = '{0}'", backupLocation); SqlDataReader _sqlDataReader = SqlHelper.ExecuteReader("BookssortedSQLDbConnection", CommandType.Text, _commandText); 

If I run the command in SSMS, it will return. "The backup set in file 1 is valid." but how can I return this message to my code?

The reader will not work because the rows are not returned.

NOTE. I tried the SMO.Restore object to try and test it, but it does not work, and that is why I do it this way.

 _restore.SqlVerify(srv, out _errorMessage); //returns false even though bakcup is fine 

BTW - open to suggestions as I don't think this is the perfect way to achieve what I'm trying to do

Answer 1

Informational messages (with a severity of less than 10) and PRINT output are returned to the client and are expressed as InfoMessage events by the SqlConnection instance. Each event contains a collection of SqlError objects (this is the same class as in SqlException.Errors ).

Here is a complete example showing communication state changes, informational messages, and exceptions. Note that I use ExecuteReader instead of ExecuteNonQuery , but the results of the information and exceptions are the same.

 namespace Test { using System; using System.Data; using System.Data.SqlClient; public class Program { public static int Main(string[] args) { if (args.Length != 2) { Usage(); return 1; } var conn = args[0]; var sqlText = args[1]; ShowSqlErrorsAndInfo(conn, sqlText); return 0; } private static void Usage() { Console.WriteLine("Usage: sqlServerConnectionString sqlCommand"); Console.WriteLine(""); Console.WriteLine(" example: \"Data Source=.;Integrated Security=true\" \"DBCC CHECKDB\""); } public static void ShowSqlErrorsAndInfo(string connectionString, string query) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.StateChange += OnStateChange; connection.InfoMessage += OnInfoMessage; SqlCommand command = new SqlCommand(query, connection); try { command.Connection.Open(); Console.WriteLine("Command execution starting."); SqlDataReader dr = command.ExecuteReader(); if (dr.HasRows) { Console.WriteLine("Rows returned."); while (dr.Read()) { for (int idx = 0; idx < dr.FieldCount; idx++) { Console.Write("{0} ", dr[idx].ToString()); } Console.WriteLine(); } } Console.WriteLine("Command execution complete."); } catch (SqlException ex) { DisplaySqlErrors(ex); } finally { command.Connection.Close(); } } } private static void DisplaySqlErrors(SqlException exception) { foreach (SqlError err in exception.Errors) { Console.WriteLine("ERROR: {0}", err.Message); } } private static void OnInfoMessage(object sender, SqlInfoMessageEventArgs e) { foreach (SqlError info in e.Errors) { Console.WriteLine("INFO: {0}", info.Message); } } private static void OnStateChange(object sender, StateChangeEventArgs e) { Console.WriteLine("Connection state changed: {0} => {1}", e.OriginalState, e.CurrentState); } } } 

Answer 2

It is too difficult to get an ssms message for an external application. However, you can write the message to a text file, and then read the data from the file.

 declare @cmd varchar(1000) SET @cmd = 'osql -S YourServer -E -d YourDatabase -q "RESTORE VERIFYONLY FROM DISK=''c:\yourBackup.bkp''" -oc:\result.txt' EXEC master.dbo.xp_cmdshell @cmd 

You can execute the above sql statements from your application and then read the result from result.txt