Take the 2-minute tour ×

Summary: I'm trying to write a text string to a column of type varchar(max) using ODBC and SQL Server 2005. It fails if the length of the string is greater than 8000. Help!

I have some C++ code that uses ODBC (SQL Native Client) to write a text string to a table. If I change the column from, say, varchar(100) to varchar(max) and try to write a string with length greater than 8000, the write fails with the following error


So, can anyone advise me on if this can be done, and how?

Some example (not production) code that shows what I'm trying to do:

SQLRETURN iError = SQLAllocEnv(&hEnv);

SQLAllocConnect(hEnv, &hDbc);

const char* pszConnStr = "Driver={SQL Server};Server=;Database=MyTestDB";
SWORD iConnectOutLen = 0;
iError = SQLDriverConnect(hDbc, NULL, (unsigned char*)pszConnStr,
                      SQL_NTS, szConnectOut,
                      (SQL_MAX_MESSAGE_LENGTH-1), &iConnectOutLen,

iError = SQLAllocStmt(hDbc, &hStmt);

const char* pszSQL = "INSERT INTO MyTestTable (LongStr) VALUES (?)";
iError = SQLPrepare(hStmt, (SQLCHAR*)pszSQL, SQL_NTS);

char* pszBigString = AllocBigString(8001);
iError = SQLSetParam(hStmt, 1, SQL_C_CHAR, SQL_VARCHAR, 0, 0, (SQLPOINTER)pszBigString, NULL);

iError = SQLExecute(hStmt);  // Returns SQL_ERROR if pszBigString len > 8000

The table MyTestTable contains a single colum defined as varchar(max). The function AllocBigString (not shown) creates a string of arbitrary length.

I understand that previous versions of SQL Server had an 8000 character limit to varchars, but not why is this happening in SQL 2005?

Thanks, Andy

share|improve this question

2 Answers 2

up vote 1 down vote accepted

You sure you load the SQL Native Driver for 2005, not the old driver for 2000? The native driver name is {SQL Server Native Client 10.0} for 2k8 or {SQL Native Client} for 2k5

The error message ODBC SQL Server Driver seem to indicate the old 2k driver (I may be wrong, haven't touch ODBC in like 10 years now).

share|improve this answer
Thank you Remus! You were right, I was using the wrong driver. When I changed that, it worked. For the record, I used the following connection string: Driver={SQL Native Client}; Server=; Database=MyTestDB; which is not quite the same as the driver name you gave, but your sugestion pointed me to the answer. Thanks again - much appreciated. Andy –  Andy Johnson Jul 2 '09 at 15:23
Apparently I can't surpass the challenge of copy paste a name from MSDN to an answer lol. Right, the name is {SQL Native Client}, I'll fix my post too, for other google sake. –  Remus Rusanu Jul 2 '09 at 15:29

Turns out that although the fix works for SQLSetParam, it does not work for SQLBindParameter.

For example:

int iLength = 18001;
char* pszBigString = new char[iLength + 1];
memset(pszBigString, 'a', iLength);
pszBigString[iLength] = 0;
::SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT,
                iLength, 0, pszBigString, iLength * sizeof(TCHAR),

will result in the same 22001 "String data, right truncation" error, regardless of which driver is used.

In fact, my experiments have shown that you do not actually need to install version 10 of the client driver. Instead you should use SQL_LONGVARCHAR instead of SQL_VARCHAR if you expect the lengths of your strings to exceed 8000 characters. You could potentially perform a mass find-and-replace, but it's possible that using SQL_LONGVARCHAR might incur some sort of penalty (although that's pure speculation; it's an 'extended datatype').

I have tested this successfully with both drivers on Windows XP:

  • {SQL Server} 2000.85.1117.00 (04/08/2004)
  • {SQL Server Native Client 10.0} 2007.100.1600.22 (10/07/2008)
share|improve this answer
Thanks, Balint! –  Andy Johnson Apr 1 '10 at 10:18

Your Answer


