Re: Using parameters: Get SQL sent to database

luxspes wrote: 
Armin Zingler wrote:

(A Gentle Introduction to ADO.NET 2.0 Trace Facilities):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp 


Copy&Pasted from (A Gentle Introduction to ADO.NET 2.0 Trace Facilities):

Using Tracing to Debug a Parameter Binding Problem

Now that we've gone through the quick overview of tracing, I'd like to present a simple use case. I'd often use ODBC trace to do problem determination when an application would "eat" a rich error message and produce a polite but fairly "information-free" message. Such application code would look like this:

string s = GetConnectionStringFromConfigFile();
using (SqlConnection conn = new SqlConnection(s))
using (SqlCommand cmd = new SqlCommand(
  "select * from authors where au_id = @auid", conn))
{
  // the error is hardcoded here but could have come from suboptimal
  // editing in a graphic user interface
  cmd.Parameters.Add("@auid", SqlDbType.Int);
  cmd.Parameters[0].Value = 123456789;
  SqlDataReader rdr = null;
  try {
   // some code that could fail goes here
  conn.Open();
  rdr = cmd.ExecuteReader();
  while (rdr.Read())
     Console.WriteLine(rdr[0]);
  rdr.Close();
  }
  catch (Exception e) {
    MessageBox.Show("polite error message");
  }
}

In this case, the error was caused by a parameter type mismatch, and the person diagnosing the error might not have access to the source code of the program. Turning on the trace, we'll see output like this:

"enter_01 <sc.SqlCommand.ExecuteReader|API> 1#"
 "<sc.SqlCommand.get_Connection|API> 1#"
 "<sc.SqlCommand.get_Connection|API> 1#"
 "<sc.TdsParser.CreateSession|ADV> 1# created session 3"
 "<sc.TdsParserSessionPool.CreateSession|ADV> 1# adding session 3 to pool"
 "<sc.TdsParserSessionPool.GetSession|ADV> 1# using session 3"
 "<sc.TdsParser.GetSession|ADV> 1# getting session 3 from pool"
 "<sc.SqlCommand.ExecuteReader|INFO> 1#  Command executed as RPC."
 "<sc.SqlCommand.get_Connection|API> 1#"
 "leave_01"
 "enter_01 <sc.SqlDataReader.Read|API> 1#"
 "<sc.SqlError.SqlError|ERR> infoNumber=245  errorState=1  errorClass=16
errorMessage='Syntax error converting the varchar value '172-32-1176' to a
column of data type int.'  procedure=''  lineNumber=1"
 "leave_01"

This shows us directly that there is a parameter value mismatch. The sample and the trace file are provided in the article code. Note that the trace file is much more compact in this case because we're only tracing with the System.Data.1 provider.
.

Relevant Pages

  • [NT] ASP.NET Session Information Leakage
    ... you can set up a Trace parameter ... Session Id: Request Type: POST ... aspx.page Begin ProcessPostData Second Try 0.207433 0.205864 ... Accept image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, ...
    (Securiteam)
  • Re: Application, session and performance.
    ... individual SQL statements between each COMMIT. ... It takes some practice learning how to read 10046 trace files. ... much of the useful data contained in the trace file is either ... a session, while recording wait events and bind variables: ...
    (comp.databases.oracle.server)
  • Re: Setting SQL trace on another session
    ... I have always had problems with MAX_DUMP_FILE_SIZE (in regards to tracing someone else's session on Sun Solaris, 8i, 9i and 10g) and have found that the only reliable way to set it is via the init.ora. ... when you start the trace and it hits a limit you might need to reset it somehow. ... I now only use oradebug when tracing someone else's session and it works so much better! ...
    (comp.databases.oracle.server)
  • Re: Application, session and performance.
    ... individual SQL statements between each COMMIT. ... While TKPROF can analyze 10046 trace ... much of the useful data contained in the trace file is either ... a session, while recording wait events and bind variables: ...
    (comp.databases.oracle.server)
  • Re: Windows Authorization
    ... I am running from IIS on the Windows 2003 Web Server Edition. ... This section sets the authorization policies of the ... Set trace enabled="true" to enable application trace logging. ... a session can be tracked by ...
    (microsoft.public.dotnet.framework.aspnet)
Loading