SQL Server, PHP and Truncating Text

PHP and SQL Server are a powerful combination, however sometimes data stored in a text type column is truncated for no apparent reason after 4096 characters. Here's how to fix the problem.

PHP can work with a large number of database systems, most common of which is MySQL. However, SQL Server is a very poweful alternative to MySQL, and well worth considering for larger projects.

However, settings for the mssql extension in PHP can easily be overlooked, resulting in strange behaviour. One of the most unusual problems I encountered on a recent project was that every time text was pulled from a column of type "text", it was truncated at around 4000 characters.

If you are experiencing a similar problem, there are a few ways to work around it.

You need to increase the maximum size of a text column to be returned from SQL Server by PHP. You can do this with a simple SQL query:

SET TEXTSIZE 2147483647

Which you can run with the following PHP (best run just after you make a connection).

mssql_query("SET TEXTSIZE 2147483647");

A better way to work around the issue is to change the "textlimit" and "textsize" settings within php.ini, like so:

mssql.textlimit = 2147483647 mssql.textsize = 2147483647

In the above examples, I have used "2147483647" (number of bytes) for the value. You don't need to set it this high necessarily, however there is no real harm in doing so.

And why does this not happen with ASP? When you use Microsoft's SQL Server ODBC driver and OLE DB Provider, textsize is automatically set to 2147483647 when a connection is made.