• Article

How to: Log Long-Running Queries (ODBC)

A complete sample is available that shows the creation of a long-running query log file. The complete sample code is in the file LogLongRunningQuery.cpp, which you can download from the SQL Server Downloads page on MSDN. This sample was developed using Microsoft Visual C++ 2005 and was developed for ODBC version 3.0 or later.

Security noteSecurity Note

When possible, use Windows Authentication. If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 crypto API.

To log long-running queries using ODBC Administrator

  1. In Control Panel, double-click Administrative Tools and then double-click Data Sources (ODBC). (Alternatively, you can run odbcad32.exe from the command prompt.)

  2. Click the User DSN, System DSN, or File DSN tab.

  3. Click the data source for which to log long-running queries.

  4. Click Configure.

  5. In the Microsoft SQL Server Configure DSN Wizard, navigate to the page with Save long-running queries to the log file.

  6. Select Save long-running queries to the log file. In the box, place the name of the file where the long-running queries should be logged. Optionally, click Browse to browse the file system for the query log.

  7. Set a query time-out interval, in milliseconds, in the Long query time (milliseconds) box.

To log long-running queries data programmatically

  1. Call SQLSetConnectAttr with SQL_COPT_SS_PERF_QUERY_LOG and the full path and file name of the long-running query log file. For example:

    C:\\Odbcqry.log

  2. Call SQLSetConnectAttr with SQL_COPT_SS_PERF_QUERY_INTERVAL and set to the time-out interval, in milliseconds.

  3. Call SQLSetConnectAttr with SQL_COPT_SS_PERF_QUERY and SQL_PERF_START to start logging long-running queries.

  4. Call SQLSetConnectAttr with SQL_COPT_SS_PERF_QUERY and SQL_PERF_STOP to stop logging long-running queries.