How to write to a varchar(max) column using ODBC

Question

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

[Microsoft][ODBC SQL Server Driver]String data, right truncation

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:

SQLHENV hEnv = NULL;
SQLRETURN iError = SQLAllocEnv(&hEnv);

HDBC hDbc = NULL;
SQLAllocConnect(hEnv, &hDbc);

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

HSTMT hStmt = NULL;
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

Answer 1

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).

Answer 2

Hi Andy & Remus,

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;
LONG_PTR lLength = SQL_NTS;
::SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT,
                SQL_C_CHAR,
                SQL_VARCHAR,
                iLength, 0, pszBigString, iLength * sizeof(TCHAR),
                &lLength);

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)