Display SQL SERVER Message,Using InfoMessage Event from SQL SERVER, to troubleshoot stored procedure

Big stored procedures which are building QUERY dynamically according to parameters are used sometime for large web applications.

Bad luck starts when such procedure gives error, and as query itself build at run time, user may frustrates catching the right error.

Also,Debugging becomes challenging with it.

One way is to use PRINT statement at end of procedure, so when stored procedure executed it displays DYNAMIC QUERY.

In SQL Server Management studio we can easily view the printed query.

But what if you want it in ASP.NET environemt?

SqlConneciton class has
SqlConnection.InfoMessage event, which occurs, when SQL Servers returns a warning or informational message.

First create ‘SqlConnection’ object.

string connectionString = “Your Connection String…”;
SqlConnection myConn = new SqlConnection(connectionString);
Then, Relate method to event.

myConn.InfoMessage += new SqlInfoMessageEventHandler(MyConnection _InfoMessage);

 

Your method may look like following:
System.Text.StringBuilder sbLog = new System.Text.StringBuilder();

void MyConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
sbLog.AppendLine(e.Message);
}

Execute following code and test output with ‘sbLog.ToString()’.

SqlCommand cmd = new SqlCommand();

if (cn.State != ConnectionState.Open)
cn.Open();

cmd.Connection = myConn;
cmd.CommandText = “SP_That_Prints_Query”;

cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

 

‘sbLog.ToString()’ will give you dynamic query generated in your stored procedure.

 

Advertisements

7 thoughts on “Display SQL SERVER Message,Using InfoMessage Event from SQL SERVER, to troubleshoot stored procedure

  1. “Display SQL SERVER Message,Using InfoMessage Event
    from SQL SERVER, to troubleshoot stored procedure
    | borania siddharth – web developer,rajkot, gujarat, india” Window Blind was in
    fact a good posting. If merely there was much more weblogs such
    as this specific one in the actual net. Anyway,
    thanks for your time, Victorina

  2. I absolutely love your blog and find the majority of your post’s to be exactly I’m looking for.
    Would you offer guest writers to write content for yourself?
    I wouldn’t mind producing a post or elaborating on many of the subjects you write in relation to here. Again, awesome web site!

  3. With havin so much written content do you ever run into any issues of plagorism or copyright violation?
    My blog has a lot of unique content I’ve either created myself or outsourced but it appears a lot of it is popping it up all over the internet without my agreement. Do you know any techniques to help protect against content from being stolen? I’d really appreciate it.

  4. Fantastic website you have here but I was wanting to know if you knew of
    any forums that cover the same topics discussed here?
    I’d really like to be a part of online community where I can get
    responses from other experienced people that share the same interest.
    If you have any suggestions, please let me
    know. Bless you!

  5. Pingback: Use of CASE END in ms sql query to get conditional result column. | borania siddharth - web developer,rajkot, gujarat, india

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s