Microsoft small business knowledge base

Article ID: 319243 - Last Review: September 6, 2012 - Revision: 7.0

Hotfix Download Available
View and request hotfix downloads
This article was previously published under Q319243

On This Page


When you use an ANSI ODBC driver, the ODBC Driver Manager may incorrectly return SQL_NO_DATA_FOUND for a column that is defined as VARCHAR or CHAR and that has an empty string in it. The problem does not occur if the column contains some data or a "NULL" value. The problem only occurs if the data is an empty string and you bind it from the client side with SQL_C_WCHAR.


The ODBC Driver Manager incorrectly assumes that the driver does not have any data when it tries to convert the empty string from ANSI to UNICODE.


To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211  ( ) INF: How to Obtain the Latest SQL Server 2000 Service Pack


A simple workaround for this problem is to bind the column as SQL_C_CHAR instead of SQL_C_WCHAR.


Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

More information

Legacy applications that use an ANSI ODBC driver may not experience this problem, because they may not bind the column as SQL_C_WCHAR. Because the Microsoft ODBC .NET Provider binds character columns as SQL_C_WCHAR by default, you may experience this problem if you use the Microsoft ODBC .NET Provider with an ANSI driver. Note that the Microsoft ODBC .NET Provider returns "NO_DATA" when this problem occurs.

Steps to Reproduce Behavior

The following native sample demonstrates the problem:
  1. Use the following script to create a table and insert one record:
    Create Table Test (ID integer, COL1 CHAR(10))
    Insert Into Test VALUES (1,'')
  2. Run the following C program:
    #include <windows.h>
    #include <stdio.h>
    #include <sql.h>
    #include <sqlext.h>
    int main(int argc, char* argv[])
    	SQLCHAR*	theDiagState = new SQLCHAR[50];
    	SQLINTEGER	theNativeState;
    	SQLCHAR*	theMessageText  = new SQLCHAR[255];
    	SQLSMALLINT	iOutputNo;
    	SQLHENV		m_SQLEnvironment;
    	SQLHDBC		m_SQLConnection;
    	SQLHSTMT	m_SQLStatement;
    	SQLRETURN	iReturn;
    	long		lStrLen;
    	char		theData[1000];
    	iReturn = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);
    	iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
    	iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);
    	char	szConnBuffer[1024];
    	short   cbOutConn;
    	char    szConnStr[255];
    	iReturn= SQLDriverConnect(m_SQLConnection, NULL,
    (SQLTCHAR*) szConnStr,strlen(szConnStr), 
    (SQLCHAR*) szConnBuffer,1024, &cbOutConn, SQL_DRIVER_NOPROMPT);
    	if (iReturn != SQL_SUCCESS)
    	//Run query.
    	iReturn = SQLAllocHandle(SQL_HANDLE_STMT,m_SQLConnection,&m_SQLStatement);
    	//The Test table contains COL1 column which is set to empty for the first record.
    	iReturn = SQLExecDirect(m_SQLStatement, (SQLCHAR*) "Select ID,COL1 FROM Test",SQL_NTS);
    	iReturn = SQLFetch(m_SQLStatement);
    	iReturn = SQLGetData(m_SQLStatement,2,SQL_C_WCHAR,theData,100,&lStrLen);
    	//The problem does not occur if you bind the same column to SQL_C_CHAR.
    	//iReturn = SQLGetData(m_SQLStatement,2,SQL_C_CHAR,theData,100,&lStrLen);
    	if (iReturn != SQL_SUCCESS)
    		//iReturn will be 100 SQL_NO_DATA_FOUND here with NO other message.
    		int CheckHere;
    		CheckHere = 0;  //It will come here.
    		if (iReturn == SQL_NO_DATA_FOUND)
    			printf("SQL_NO_DATA_FOUND has been returned.\n Press Enter key to continue...\n");
    	iReturn = SQLFreeHandle(SQL_HANDLE_STMT,m_SQLStatement);
    	iReturn = SQLDisconnect(m_SQLConnection);
    	iReturn = SQLFreeHandle(SQL_HANDLE_DBC,m_SQLConnection);
    	iReturn = SQLFreeHandle(SQL_HANDLE_ENV,m_SQLEnvironment);
    	m_SQLStatement = NULL;
    	m_SQLConnection = NULL;
    	m_SQLEnvironment = NULL;
    	delete theMessageText; 
    	delete theDiagState;
    	return 1;
    You receive the following message:
    SQL_NO_DATA_FOUND has been returned.
    Press Enter key to continue...

Applies to
  • Microsoft Data Access Components 2.7
kbhotfixserver kbqfe kbdownload kbsqlserv2000sp3fix kbbug kbfix kbsqlserv2000presp3fix KB319243
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support