Looking at actual SQL statement that is run against the server

[Jason]

I have run into this issue a number of times. I have complex parameterized
commands that I am running against an access database. When I get errors
there seems to be no way of seeing the actual SQL statement that is being
run against the server-- i.e., the parameters are replaced by the actual
values.

When I build applications against SQL server I use the SQL trace program on
the server to see the statements. But it would be great if I could somehow
get that in the code, so I could write it to a log or something.

For example:

mycommand.commandtext returns the SQL statement with the parameter
placeholders.

It would be great if there was a way to call:

mycommand.actualSQL and have it return the statement with the parameters
replaced by their coresponding values. Or maybe have it
mydataadapter.update() throw an exception with the actualSQL statement when
the update fails.

How do you all handle this? Am I missing something simple.

Thank you in advance.

 

[hb]

I have run into this issue a number of times. I have complex parameterized
commands that I am running against an access database. When I get errors
there seems to be no way of seeing the actual SQL statement that is being
run against the server-- i.e., the parameters are replaced by the actual
values.

When I build applications against SQL server I use the SQL trace program on
the server to see the statements. But it would be great if I could somehow
get that in the code, so I could write it to a log or something.

For example:

mycommand.commandtext returns the SQL statement with the parameter
placeholders.

It would be great if there was a way to call:

mycommand.actualSQL and have it return the statement with the parameters
replaced by their coresponding values. Or maybe have it
mydataadapter.update() throw an exception with the actualSQL statement when
the update fails.

How do you all handle this? Am I missing something simple.

Here's what I do for a data adapter update. I add an eventhandler for
RowUpdating and it looks like this:

public void daUpdatingHandler( object sender, OracleRowUpdatingEventArgs e)
{
HttpContext.Current.Trace.Write( "Row_Updating", e.Command.CommandText );
foreach( OracleParameter oraParam in e.Command.Parameters ) {
HttpContext.Current.Trace.Write( oraParam.SourceColumn,
oraParam.Value.ToString() );
}
}

I can then look at the trace file, see the parameterized SQL and then the
values to be substituted.

 

[Kevin Yu [MSFT]]

Thanks for hb's quick response.

Hi Jason,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you would like to trace the SQL
statements executed on the server programatically, so that they can be
written into the log. If there is any misunderstanding, please feel free to
let me know.

hb has provided us with a good solution to get the SQL statements in
DataAdapter.RowUpdating. Besides, we can also use the trace provided by SQL
server itself.

You can use a set of stored procedures that Microsoft provides with SQL
Server 2000 to define which events you want to trace, what data to collect,
and where to save the collected information. You can specify event filters
to reduce the amount of information the trace collects and stores, as you
can when you use Profiler. However, when you define server-side traces, you
must specify numbers instead of Profiler's meaningful names for the events
and data columns.

Here is a good technical artical which tell us how to use these trace
functions programatically.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/
html/trackingdowneventclues.asp

For more information, please check the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht
_trace_1nci.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_mon_perf_227n.asp

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jason]

Thank you for your reply; However I am not using SQL Server in this case.
I am running the application against an ACCESS database. Debugging becomes
difficult since I can not use the Microsoft SQL Server profiler as I
normally do.

Thanks,

Jason

 

[Kevin Yu [MSFT]]

Hi Jason,

Since you are working on an Access database, it seems the only way of
tracing the SQL statement run on the server is using e.Command.CommandText
and e.Command.Parameters provided by hb, because Access doesn't provide
such functions for tracing.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Jason]

I realize that. Thank you for your reply. You might have the ado.net team
add that to the "improvement list". For example a function that returns the
command statement with the parameters filled in.

mycommand.commandtext = "Select * from mytable where userid = '?'
mycommand.parameters("userid") = "jdoe"
Then mycommand.actualcommand would return the string:

"Select * from mytable where userid = 'jdoe'"

Just a thought.

Thanks,

Jason

 

[Kevin Yu [MSFT]]

Hi Jason,

Thank you very much for your feedback. If you have suggestions that will
make our products better, please send mail directly to
(e-mail address removed). Your suggestions will be highly appreciated. I will
also forward this to the appropriate team. Thanks again!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."