Chris posted some time ago a code example on how to get the text messages that SQL Server sometimes send you.

Today I used that code, and I couldn’t help but changing it around a bit. Chris’ example used an instance variable to store the StringBuilder he used to build the full message. I didn’t like this since it stopped this code from being able to run concurrently, so I changed it a little.

Instead of using a real method, I used .NET2 anonymous methods that let me change variabels in method scope. I don’t know what kind of voodoo-magic fuels this, but it sure works great. Here you go.

[csharp]
public string GetDbccPage( string database, int file, int page ) {
StringBuilder infoMessage = new StringBuilder();

using (SqlConnection conn =
new SqlConnection( connectionString ))
{
conn.Open();

using( SqlCommand cmd = conn.CreateCommand() ) {
cmd.CommandType = CommandType.Text;
cmd.CommandText = “DBCC TRACEON (3604)”;

cmd.ExecuteNonQuery();
}

conn.InfoMessage +=
delegate( object sender, SqlInfoMessageEventArgs e ) {
infoMessage.Append( e.Message );
};

using( SqlCommand cmd = conn.CreateCommand() ) {
cmd.CommandType = CommandType.Text;
cmd.CommandText =
String.Format( “DBCC PAGE ({0}, {1}, {2}, 0)”, database, file, page );

cmd.ExecuteNonQuery();
}
}
return infoMessage.ToString();
}
[/csharp]

Posted by Andres on Friday, November 11th, 2005 at 17:47.