How to get raw query that the ODBC driver tries to execute?

Question

I'm using C++ to query a SQL Server 2005 database using ODBC. The queries contain parameters that I set using SQLSetParam(). I run the queries by calling SQLExecute().

Occasionally a query will fail, and I need to log the context of the failure. I am calling SQLError() to get the error information, but I would also like to log the raw query that the ODBC driver tried to run - that is, the query with the parameter values expanded-out. Can anyone advise if there is a way to get access to this?

The context is error logging in a production environment. I'm not "there" when the error happens, so I can't use the profiler or similar tools. Thats why I want to log as much information as possible for analysis later.

Answer 1

There isn't. To my understanding, that is not how ODBC works: the parameters don't get "expanded" at all and are sent separately to the server.

For your logging situation, simply convert the parameters to strings ad-hoc and insert them in the statement text before writing it out to the log file. That's how we do it in our framework, and it works fine. For some parameter types you'll have to decide whether to favor human-readable logging or copy-paste-able into sql server for reproduction.

Answer 2

Have a look with SQL Profiler to see what is actually being sent to the server?

Our APP calls SProcs (so somewhat different) and we log all the Sproc calls, and what parameters were sent; we then update the log with the return value from the Sproc (so we can report on log entries that have NO return result where there was some sort of catastrophic failure)

We can then re-run any failed SProcs with the parameters they were given to see what results we get.

When we re-run them we wrap them with

BEGIN TRANSACTION

EXEC MySproc @FOO='BAR', ...

ROLLBACK

so that they don't make any changes to the DB

Answer 3

You can enable ODBC tracing on the client via the control panel, but be prepared for a major performance hit and some very large log files to read.