Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Why do I get corrupted TEXT columns back from Microsoft SQL Server?

The customer who reported this to us was attempting to retrieve multiple SQL Server TEXT columns from PHP. The second TEXT column always contained garbage. This problem only appears to happen when retrieving multiple TEXT fields from a table with SQLGetData and occurs for the second TEXT column retrieved. PHP sees that TEXT fields in SQL Server can be very long so instead of binding the column (as usual) it uses SQLGetData. This problem affects any application whose ODBC interface uses SQLGetData for multiple TEXT fields.

The specific report involved a table created as follows:

create table BENCH_TEXT (f1 integer, f2 text, f3 text)
insert into BENCH_TEXT values(1, 'some text', 'some text')
select f1, f2, f3 from BENCH_TEXT

The f1 and f2 columns are successfully retrieved with SQLGetData, but when the f3 column is requested with SQLGetData, the returned StrLen_or_IndPtr value is usually too short and the data is garbage.

After searching the web, we discovered a similar report by Chad Slater-Scott, which read:

"Subject: BUG: Service Pack 1 on SQL7.0 (SQLExtendedFetch Returns metadata on SQLGetData)

Date: 09/27/1999

Author: Chad Slater-Scott <avacado@usa.net>

Just wanted to let you all know that Service Pack 1 (SP1) For SQL Server has a new bug. After using SQLExtendedFetch or SQLFetchScroll to retrieve records using SQL_ABSOLUTE or SQL_RELATIVE positioning, a SQLGetData call to a TEXT field will return garbage. This is not reproducible if the TEXT field is the only field selected or if you the TEXT field is the first field called. In order to reproduce it, another field value must be retrieved using SQLGetData. I reported this to Microsoft and a Bug has been filed. If any of you are using this sequence of calls and have installed SP1, check your stuff for garbage on the TEXT fields. The Bug ID is 56509 is want to track its status."

We've been told that the problem is in the SQL Server ODBC driver included in SP1 for SQL Server and SP6 for NT. The version with the problem appears to be drive:\winnt\system32\sqlsvr32.dll (3.70.06.90) and the working version is 3.70.0623.

Our machines running SQL Server 7 and NT 4 SP4 don't seem to have this problem.