Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

FIX: Microsoft Access/Jet ODBC Driver returns size of 2 gigabytes for computed text columns


View products that this article applies to.

This article was previously published under Q272951

↑ Back to the top


Symptoms

If you use the Microsoft Access (Jet) ODBC driver (Odbcjt32.dll version 4.0.5303.1), which is shipped with Microsoft Windows 2000 Service Pack (SP) 1, when you run statements that contain computed columns of a text data type, a call to the SQLDescribeCol ODBC API on the computed columns returns a column size of 2147483598 (2 GB).

Applications that try to use the column size returned from the call to the SQLDescribeCol ODBC API, to determine how much memory to allocate for the column, fail with "Out of memory" error messages.

Odbcjt32.dll version 4.0.5303.1 is also installed by Microsoft Data Access Components (MDAC) version 2.5, Service Pack 1.

The problem may also occur in other Access ODBC drivers.

For more information about this problem in Windows Vista, click the following article number to view the article in the Microsoft Knowledge Base:
940895� On a Windows Vista-based computer, the SQLDescribeCol function and the SQLColAttribute function return the incorrect value of 2147483598 instead of the expected value of 255

↑ Back to the top


Cause

Jet returns a column size of zero for computed columns, which the Jet ODBC driver interprets as an indication of a Long data type. Because of this, the Jet driver sets the column size to the maximum size allowed for Long data types, which is 2 GB. However, if the data type is a text data type (character or varchar) the type is still reported as SQL_VARCHAR, which should have a maximum size of 255.

An example taken from an ODBC trace follows that demonstrates the returned 2 GB column size as the seventh (7th) argument:

mdactest        65c-698	EXIT  SQLDescribeCol  with return code 0 (SQL_SUCCESS)
		HSTMT               01EC18D8
		UWORD                        2 
		UCHAR *             0x0012FAE4 [       8] "Expr1001"
		SWORD                      129 
		SWORD *             0x0012FA94 (8)
		SWORD *             0x0012FA86 (12)
		SQLULEN *           0x0012FA98 (2147483598)
		SWORD *             0x0012FA9C (0)
		SWORD *             0x0012FA9E (1)
				

↑ Back to the top


Resolution

To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.5. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
293312� How to obtain the latest MDAC 2.5 service pack
If you use the Access ODBC driver (Aceodbc.dll) that is included in the 2007 Microsoft Office system, apply hotfix 941877 to resolve this problem.

For more information about hotfix 941877, click the following article number to view the article in the Microsoft Knowledge Base:
941877� Description of the Access 2007 hotfix package: February 26, 2008
The English version of this fix should have the following file attributes or later:
   Date         Version        Size              File name     
   -----------------------------------------------------------
   08/29/00     4.0.5629.0     270,608 bytes     Odbcjt32.dll
				
NOTE: This hotfix is not included with either Jet SP5 or MDAC 2.6. Although the fix is expected to be included with MDAC 2.5 SP2, systems experiencing this problem must apply the hotfix to resolve this problem until MDAC 2.5 SP2 is released. Additionally, systems that are upgraded directly from MDAC 2.5 SP1 to MDAC 2.6 should also have this hotfix applied.

↑ Back to the top


Workaround

In some cases, it may be possible to wrap the calculated field in a datatype conversion function such as CInt or CLng, which converts the results to a non-text datatype.

↑ Back to the top


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft Data Access Components 2.5 Service Pack 2.

↑ Back to the top


More information

This behavior can occur with any function that returns calculated values, which includes common functions like IIF, MAX, MIN, FIRST, RIGHT, MID, and so forth.

NOTE: This hotfix also contains a fix for the issue outlined in the following Microsoft Knowledge Base article:
271177� FIX: Unable to repair password protected database through ODBC Control Panel

Steps to reproduce behavior

  1. Copy the code that follows into a Microsoft Visual C++ console application, and then compile the code. Please note that you may need to change the datasource name, user ID and password.
    #include "stdafx.h"
    #include <windows.h>
    #include <stdio.h>
    #include <sql.h>
    #include <sqlext.h>
    
    
    void ShowErrors(HDBC, HSTMT);
    
    
    void main(void)
    {
    
    	SQLHANDLE henv;
    	SQLHANDLE hdbc;
    	SQLHANDLE hstmt;
    	
    	RETCODE rc = 0;
    	
    	SQLCHAR Statement[50] = "Select IIF('A' = 'A', 'Y', 'N') from shippers";	
    
    	SQLCHAR dsn[15] = "northwind2000";
    	SQLCHAR user[6] = "admin";
    	SQLCHAR pass[5] = "";
    	long sqlnts = -3;
    	
    //	variables for SQLDescribeCol
    	char  m_colname[20] = {"\0"};
    	short m_colsize = 0;
    	short m_sqltype = 0;
    	unsigned long m_prec = 0;
    	short m_scale = 0;
    	short m_nullable = 0;
    	
    
    
    	rc = SQLAllocEnv(&henv);
    	
    	rc = SQLAllocConnect(henv, &hdbc);
    	
    	rc = SQLConnect(hdbc, dsn, SQL_NTS, (SQLCHAR *) user, SQL_NTS, (SQLCHAR *) pass, SQL_NTS);
    	if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
    		ShowErrors(hdbc, NULL);
    	rc = SQLAllocStmt(hdbc, &hstmt);
    
    	rc = SQLExecDirect(hstmt, Statement, SQL_NTS);
    	if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
    		ShowErrors(NULL, hstmt);
    
    	rc = SQLDescribeCol(hstmt, 1, (unsigned char *)m_colname, 20, &m_colsize, &m_sqltype, &m_prec, &m_scale, &m_nullable);
    
    	printf("Size returned from SQLDescribeCol is:\t%d", m_prec);
    
    	SQLFreeStmt(hstmt, SQL_CLOSE);
    	SQLDisconnect(hdbc);
    	SQLFreeConnect(hdbc);
    	SQLFreeEnv(henv);
    }
    
    
    void ShowErrors (HDBC hdbc, HSTMT hstmt)
    {
    
    //	Variables for SQLGetDiagRec
    	SQLCHAR sqlState[20];
    	SQLCHAR errorMsg[1000];
    	SQLSMALLINT errorMsgLen;
    	SQLINTEGER nativeError;
    	char szTemp[4096];
    	
    	
    //	Retrieve the Raised error message
    	if (hdbc!= NULL) 
    		SQLGetDiagRec(SQL_HANDLE_DBC,hdbc,1,sqlState,
    			&nativeError,errorMsg,1000,&errorMsgLen);
    	else
    		SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,sqlState,
    			&nativeError,errorMsg,1000,&errorMsgLen);
    
    //	Display the size of the returned error message, and the message itself
    	sprintf(szTemp, "Length=[%d] Text=%s", strlen((char*)errorMsg), errorMsg );
    	printf("ERROR!  %s\n\n\n", szTemp);
    
    	printf("Press any key to exit...");
    	getchar();
    
    	if (hstmt != NULL)
    	{
    		SQLFreeStmt(hstmt, SQL_CLOSE);
    		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    	}
    	SQLDisconnect(hdbc);
    	SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    
    	exit(0);
    
    
    }
    					
  2. Run the code, and note that the size of the value returned for the precision is 2 GB.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
271177� FIX: Unable to repair password protected database through ODBC Control Panel

↑ Back to the top


Keywords: kbhotfixserver, kbbug, kbdatabase, kbfix, kbjet, kbmdac250sp2fix, kbmdacnosweep, kbqfe, KB272951

↑ Back to the top

Article Info
Article ID : 272951
Revision : 6
Created on : 4/28/2008
Published on : 4/28/2008
Exists online : False
Views : 354