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

Article translations Article translations
Article ID: 272951 - View products that this article applies to.
This article was previously published under Q272951
Expand all | Collapse all

On This Page

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

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)
				

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.

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.

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.

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

Properties

Article ID: 272951 - Last Review: April 28, 2008 - Revision: 5.1
APPLIES TO
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 Service Pack 1
  • Microsoft Open Database Connectivity Driver for Access 4.0
Keywords: 
kbqfe kbhotfixserver kbbug kbdatabase kbfix kbjet kbmdac250sp2fix kbmdacnosweep KB272951

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com