SQL Profiler

Dear All,

This is my third Post.Today i going to explorer one of the most useful and troubleshooting tool provided with SQL i.e. SQL Profiler. it is best tool to check what is going on your SQL server i.e. which query or Stored Procedure running with what parameter etc. or when written SQL logic works unexpectedly.

Permissions Required to Run SQL Server Profiler:

To run SQL Server Profiler, users must be granted the ALTER TRACE permission.

Let learn more with simple example.code.jpg

I have created simple console Application. i call stored procedure(SP) with Name STP_ADD.this stored procedure accept one parameter @FirstName.

Now suppose i want check how this SP called with parameter value.To achieve this let follow following step

1.Open the SQL Profiler or type profiler in run prompt..

profiler-screenshot7.jpg

2.Select Tuning Template.

profiler-screenshot1.png

3.Click on Event Selection Tab.again click on Column Filters.

4.Click on DataBaseName to Enter Database name on which you wish to run Profiler. Enter DB Name there i.e. %DB Name%.

profiler-screenshot2.png

5.Click on TextData to Enter SP name on which you wish to run Profiler. Enter SP Name there i.e. %SP Name%.

profiler-screenshot3.jpg

6.Hit on Run Button.Now run your console which i created Earlier

using (SqlConnection con = new SqlConnection("Data Source=PRAMOD-PC;Initial Catalog=EntityStudy;Integrated Security=True"))
            {
                using (SqlCommand cmd = new SqlCommand("STP_ADD", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FirstName", "vishal");
                    con.Open();
                    int i = cmd.ExecuteNonQuery();
                    con.Close();
                }
            }

7.Check the profiler output. output will be

Profiler ScreenShot4

Conclusion

SQL Profiler is one of best troubleshooting tool for checking how SQL stored procedure or inline Queries get called with parameter values. it is very useful when sometime Stored Procedure return unexpected  output.

SQL Profiler has very wide Scope.I just try to cover one of the basic part of it.

 

Thanks,

 

 

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