The SET SHOWLPLAN_ALL statement and the Microsoft SQL Server ODBC driver for SQL Server 7.0 and SQL Server 2000 (279801)

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 "Select * from Authors" query against the pubs database.

#include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <odbcinst.h>
#include <sqlext.h>
#include <iostream>
using namespace std;


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*) "Pubs", SQL_NTS,
                  (SQLCHAR*) "sa", SQL_NTS,
                  (SQLCHAR*) "", SQL_NTS);

	//connect to database
		
	

	/* Allocate statement handle */ 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); 
	/* Prepare Statement */ 
	
	retcode = SQLExecDirect(hstmt,(SQLCHAR*)"SET SHOWPLAN_ALL ON",SQL_NTS);
	retcode = SQLExecDirect(hstmt,(SQLCHAR*)"Select * from Authors",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;

}