DOCUMENT:Q238774 23-AUG-2001 [odbc] TITLE :PRB: ODBC Driver Manager Error: "Program Type out of Range" PRODUCT :Open Database Connectivity (ODBC) PROD/VER::3.7 OPER/SYS: KEYWORDS:kbODBC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC260 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft ODBC Driver for SQL Server, version 3.7 ------------------------------------------------------------------------------- SYMPTOMS ======== A call to SQLBindCol may return a SQL_ERROR when you attempt to bind to the results from a SQL Server 7.0 field defined with the data type "sysname" using the Microsoft ODBC Driver for SQL Server version 3.7. The resulting message from the ODBC Driver manager indicates that the reason for the failure is: Program Type out of Range (0) The illustration of the error was generated using the default ODBC Trace DLL, where an application named MYAPP is attempting to retrieve the results from the following query in SQL Server 7.0: select name from master..sysdatabases where dbid=1 The process of managing the result set generated by the preceding query, using ODBC, requires that you use both the SQLDescribeCol and the SQLBindCol functions. SQLDescribeCol returns the result descriptor, column name, type, column size, decimal digits, and nullability, for one column in the result set. SQLBindCol is used to bind application data buffers to columns in the result set. The following list shows the ODBC Trace SQL.log entries for both the SQLDescribeCol and SQLBindCol function calls: MYAPP ab:40 ENTER SQLDescribeCol HSTMT 0x00db2360 UWORD 1 UCHAR * 0x0004ec24 SWORD 128 SWORD * 0x0004ec1c SWORD * 0x0004ec0c UDWORD * 0x0004ec08 SWORD * 0x0004eda8 SWORD * 0x0004ed94 MYAPP ab:40 EXIT SQLDescribeCol with return code 0 (SQL_SUCCESS) HSTMT 0x00db2360 UWORD 1 UCHAR * 0x0004ec24 SWORD 128 SWORD * 0x0004ec1c (4) SWORD * 0x0004ec0c (-9) UDWORD * 0x0004ec08 (128) SWORD * 0x0004eda8 (0) SWORD * 0x0004ed94 (1) NOTE: A number of ODBC API Entry/Exit Points may be displayed here. MYAPP ab:40 ENTER SQLBindCol HSTMT 0x00db2360 UWORD 1 SWORD 32767 PTR 0x000c34e4 SDWORD 0 SDWORD * 0x000c34a8 MYAPP ab:40 EXIT SQLBindCol with return code -1 (SQL_ERROR) HSTMT 0x00db2360 UWORD 1 SWORD 32767 PTR 0x000c34e4 SDWORD 0 SDWORD * 0x000c34a8 DIAG [S1003] [Microsoft][ODBC Driver Manager] Program type out of range (0) Note that SQLDescribeCol indicates that the SQLDataType being returned is (-9) and that the ColumnSize is (128). It is also important to note that the CDatatype defined in the call to SQLBindCol is 32767 or and subsequently results in the error reported by the ODBC Driver Manager. CAUSE ===== The Microsoft ODBC Driver for SQL Server 3.7 has been changed to work with the SQL_WVARCHAR, which was defined for ODBC 3.5. When retrieving results from a column or field defined with the SQL Server system data type of "sysname", SQLDescribeCol reports the result set column data type as SQL_WVARCHAR. If the application was generated using an older version of the Microsoft Data Access Components (MDAC) Software Development Kit (SDK) this newer ODBC data type may not be defined and the CDataType information provided by SQLDescribeCol and subsequently used by SQLBindCol is not valid. WORKAROUND ========== There are three possible methods to resolve or work around this problem. The first and most immediate method to deal with this problem is to execute the ODBCCMPT utility against the application. For example: odbccmpt myapp.exe The second method is to change the SQL Statement so that SQL Server 7.0 converts the column result to a data type that will be translated to an ODBC SQLDataType of SQL_VARCHAR: select convert(varchar(30),name) from master..sysdatabases where dbid = 1 -or- select cast(name as varchar(30)) from master..sysdatabases where dbid = 1 The third method is to alter the application so that it is aware of the new data types being used by SQL Server 7.0 and the SQL Server 7.0 ODBC Driver. You can accomplish this by incorporating the Sqlucode.h include file that contains the #define for SQL_WVARCHAR into the application project. The Sqlucode.h include file is located in the following directory on a SQL Server 7.0 installation where the Development Tools for SQL Server are installed: :\\DevTools\Include The Sqlucode.h file is also available from the MDAC SDK 2.1 update for MDAC SDK 2.0. This update is available for download at: http://www.microsoft.com/data/download.htm (http://www.microsoft.com/data/download.htm) MORE INFORMATION ================ SQL Server 7.0 contains a number of changes, in which one of the changes is a redefinition of the system data type "sysname" from varchar(30) to nvarchar(128) in order to support Unicode. REFERENCES ---------- SQL Server 7.0 Books OnLine; topic: "ODBCCMPT Utility" For information regarding the "sysname" data type change in SQL Server 7.0: SQL Server 7.0 Books OnLine; topic: "SQL Server Backward Compatibility Details - Backward Compatibility Details (Level 2)"; "Changes to Columns in System Tables" Additional query words: SQL_WVARCHAR SQLDescribeCol SQLBindCol ====================================================================== Keywords : kbODBC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC260 Technology : kbSQLServSearch kbAudDeveloper kbODBCSearch kbODBCSQLServ370 Version : :3.7 Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.