Database command and C#

[ZGangsta]

I'm writing a Visual C# .NET program and I need to get the results of a DBCC command into my program. When I run DBCC CONCURRENCYVIOLATION in SQL Server Management Studio, the string containing the info I need comes back in the 'Messages' pane but I need to be able to access it like I could with a SELECT statement.
I know how to set up the server/database connection in Visual Studio and can make some simple commands work into my program (such as this.sqlSelectCommand1.CommandText = "SELECT @@VERSION AS version"; ) but I'm a bit shaky with this stuff so far.

Anyone know how to solve the DBCC problem?

Thanks.

 

[mikeblas]

You need to add an InfoMessage event handler to your connection. It's pretty cumbersome, but it'll work. You can start with something like this:

using System;
using System.Collections.Generic;
using System.Text;

using System.Data.SqlClient;

namespace DBCC
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection(
                "Integrated Security=SSPI;Initial Catalog=steam;Data Source=localhost;");
            conn.Open();

            conn.InfoMessage += new SqlInfoMessageEventHandler(SqlMessageEventHandler);

            SqlCommand c = new SqlCommand("DBCC CONCURRENCYVIOLATION", conn);
            c.ExecuteNonQuery();
        }

        protected static void SqlMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
        {
            foreach (SqlError err in e.Errors)
            {
                Console.WriteLine("The {0} sent severity {1}, state {2} error number {3}\n" +
                    "on line {4} of procedure {5} on server {6}:\n{7}",
                    err.Source, err.Class, err.State, err.Number, err.LineNumber,
                    err.Procedure, err.Server, err.Message);
            }
        }
    }
}

 

[ZGangsta]

Cool, thanks for the reply, that helps. I follow that so far, but when I look at err.message, I get the string

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

and when I use err.ToString() I get

System.Data.SqlClient.SqlError: DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When I run DBCC CONCURRENCYVIOLATION as a query on the server in SQL Server Management Studio 2005 I get the message

Concurrency violations since 2007-09-25 11:38:23.797
     1     2     3     4     5     6     7     8     9  10-100  >100
     0     0     0     0     0     0     0     0     0       0     0
Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

which is what I'm trying to get into my program, so I can display the numbers in each column.
Am I missing something simple where I'm only grabbing the last line of the message? It only iterates through that foreach loop once so I only get one instance of err, so I think the Message and ToString() properties are returning the whole thing.
Any ideas why I don't get the whole message?

Thanks

 

[mikeblas]

None of the servers I have access to report any concurrency violations, so I can't test it myself any further.

 

[ZGangsta]

So I got back to looking into this.

When I take a look at the "SqlInfoMessageEventArgs e" in the watch when it gets into the SqlMessageEventHandler, the only info it's giving back (in e.Message) is the "DBCC execution completed. If DBCC printed error messages, contact your system administrator" rather than the whole message that comes back in Server Management Studio (or OSQL from cmd line) when I run DBCC CONCURRENCYVIOLATION. This happens whether or not the DB I'm testing has concurrency violations.

So any ideas why I can't get the results back?

Or if anyone knows another/better way to check if concurrency violations have occured that would be great.

Thanks again.