How to log long-running queries (ODBC)

To log long-running queries using ODBC Administrator

1. In Control Panel, double-click 32-bit ODBC.
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. Navigate the Microsoft SQL Server Configure DSN Wizard 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 fOption set to SQL_COPT_SS_PERF_QUERY_LOG and vParam set to the full path and file name of the long-running query log file. For example:

   C:\\Odbcqry.log

     

2. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_QUERY_INTERVAL and vParam set to the time-out interval, in milliseconds.
3. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_QUERY and vParam set to SQL_PERF_START to start logging long-running queries.
4. Call SQLSetConnectAttr with fOption set to SQL_COPT_SS_PERF_QUERY and vParam set to SQL_PERF_STOP to stop logging long-running queries.

Examples

This example shows the creation of a long-running query log file. Error-checking code was removed to simplify this example.

// Sample showing the SQL Server ODBC driver-specific options

// to log long-running queries. Creates C:\Odbcqry.log, which

// contains a list of queries whose execution

// exceeds an interval set by the application.

#include <stdio.h>

#include <string.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

#include <odbcss.h>

SQLHENV        henv = SQL_NULL_HENV;

SQLHDBC        hdbc1 = SQL_NULL_HDBC;    

SQLHSTMT        hstmt1 = SQL_NULL_HSTMT;

int main() {

    RETCODE retcode;

     // Allocate the ODBC environment and save handle.

    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

    // Let ODBC know this is an ODBC 3.0 app.

    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,

                            (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

    // Allocate ODBC connection handle and connect.

    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

    retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,

                    "sa", SQL_NTS, "MyPassWord", SQL_NTS));

    // Set options to log long-running queries, including the

    // file to use for the log.

    retcode = SQLSetConnectAttr

                            (hdbc1,

                            SQL_COPT_SS_PERF_QUERY_LOG,

                            &"c:\\odbcqry.log",

                            SQL_NTS);

    // Set the long-running query interval (in

    // milliseconds).  Note that for version 2.50 and 2.65

    // drivers, this value is specified in seconds, not milliseconds.

    retcode = SQLSetConnectAttr

                            (hdbc1,

                            SQL_COPT_SS_PERF_QUERY_INTERVAL,

                            (SQLPOINTER)3000,

                            SQL_IS_UINTEGER);

    // Start the long-running query log.

    retcode = SQLSetConnectAttr

                            (hdbc1,

                            SQL_COPT_SS_PERF_QUERY,

                            (SQLPOINTER)SQL_PERF_START,

                            SQL_IS_UINTEGER);

    // Allocate statement handle then execute commands.

    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);

    retcode = SQLExecDirect(hstmt1,

            "SELECT * FROM pubs.dbo.authors", SQL_NTS);

    // Clear any result sets generated.

    while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )

        ;

    retcode = SQLExecDirect(hstmt1,

            "SELECT * FROM pubs.dbo.stores", SQL_NTS);

    // Clear any result sets generated.

    while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )

        ;

    // Generate a long-running query.

    retcode = SQLExecDirect(hstmt1,

            "waitfor delay '00:00:04' ", SQL_NTS);

    // Clear any result sets generated.

    while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA )

        ;

    /* Clean up. */

    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

    SQLDisconnect(hdbc1);

    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return(0);

}

See Also