Microsoft KB Archive/279801

= The SET SHOWLPLAN_ALL statement and the Microsoft SQL Server ODBC driver for SQL Server 7.0 and SQL Server 2000 =

Article ID: 279801

Article Last Modified on 11/17/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q279801



SUMMARY
This article discusses how the Microsoft SQL Server ODBC driver returns SET SHOWPLAN data from SQL Server 7.0 or SQL Server 2000 to an ODBC application.



MORE INFORMATION
The SET SHOWPLAN statement is no longer supported in SQL Server 7.0 or SQL Server 2000. SET SHOWPLAN has been replaced by SET SHOWPLAN_TEXT (to display readable text) and SET SHOWPLAN_ALL (to display output that can be parsed more easily by an application building a report of showplan output). The output of SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL is returned not as informational messages (as in SQL Server 6.x), but as a result set. The application needs to process the output as part of the result set, not as messages returned through the ODBC SQLGetDiagRec function or the DB-Library message handler.

The SET SHOWPLAN_ALL option prevents Microsoft SQL Server from executing Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.

Code Sample
The following code returns the execution plan for a &quot;Select * from Authors&quot; query against the pubs database. using namespace std;
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 
 * 6) include

SQLHENV    henv; SQLHDBC    hdbc; SQLHSTMT   hstmt; SQLRETURN  retcode; SQLINTEGER dataID; char       *data;

void main {   data = new char[1000];

/*Allocate environment handle */ retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); /* Set the ODBC version environment attribute */ retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); /* Allocate connection handle */ retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); /* Set login timeout to 5 seconds. */    SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0); // char    szConnBuffer[1024]; // short   cbOutConn; retcode = SQLConnect(hdbc, (SQLCHAR*) &quot;Pubs&quot;, SQL_NTS,                 (SQLCHAR*) &quot;sa&quot;, SQL_NTS,                  (SQLCHAR*) &quot;&quot;, SQL_NTS);

//connect to database

/* Allocate statement handle */ retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); /* Prepare Statement */ retcode = SQLExecDirect(hstmt,(SQLCHAR*)&quot;SET SHOWPLAN_ALL ON&quot;,SQL_NTS); retcode = SQLExecDirect(hstmt,(SQLCHAR*)&quot;Select * from Authors&quot;,SQL_NTS);

/* Fetch Data */ retcode = SQLFetch(hstmt); while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) /* Get data only from columns 1*/ {           retcode = SQLGetData(hstmt, 1, SQL_C_CHAR, data, 100, &dataID); cout << data <<endl; retcode = SQLFetch(hstmt); }

//clean up   retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); retcode = SQLDisconnect(hdbc); retcode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc); retcode = SQLFreeHandle(SQL_HANDLE_ENV, henv); delete data;

}

Additional query words: SHOWPLAN_ALL SHOWPLAN_TEXT

Keywords: kbinfo KB279801

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.