How can I get the return value from Sql Server system message?

I am trying to verify the backup I have just done in c# using 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 execute the command in SSMS it returns ‘The backup set on file 1 is valid.’ but how can I get this message back into my code?

  • SQL statement to calculate an Hourly amount
  • How can I compare rows from 2 tables that have composite primary keys?
  • Exposing SQL Data to clients
  • better way to generate months/year table
  • SQL Server Reporting Service render to pdf character encoding issue
  • CASE statement alternate
  • A reader wont work as there are no rows being returned.

    NOTE: I have tried the SMO.Restore object to try and verify it but it doesn’t work and that is why I am doing 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 ideal way to achieve what I am trying to do

  • Escaping user input in SQL parameters
  • How do I use T-SQL's Exists keyword?
  • SQL Identity with leading padded zeros
  • SQL Server 2008 R2 : Cross join with Where Vs Inner Join Performance
  • “Conversion failed when converting date” while inserting CSV data
  • SQL Command to execute multiple times?
  • 2 Solutions collect form web for “How can I get the return value from Sql Server system message?”

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

    Here’s a complete example that shows connection state changes, info messages and exceptions. Note that I use ExecuteReader instead of ExecuteNonQuery, but the info and exception results 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);
            }
        }
    }
    

    Its pretty difficult to retrieve the ssms message to the front end application . However you can write the message into 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''" -o c:\result.txt'
    
    EXEC master.dbo.xp_cmdshell @cmd
    

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.