I am using several memory tables to cache data for my website. This is a local web host on my mac and a dev server on linux using mssql php library on php 5.5
A simplified table looks like:
CacheID INT NOT NULL
CacheData VARCHAR(MAX)
I occasionally insert several large values. 10,000+ characters. The insert works fine. A simple query tells me all the data is in the field:
SELECT
CacheID,
LEN(CacheData)
FROM Caches
However, when I actually select the data the cache
column is always truncated to 8192 characters causing problems. Simple select:
SELECT
CacheData
FROM Caches
WHERE CacheID = 10
I checked varchar(max) character limit. And it is well beyond 8192. 2^32 - 1
Why is the data being Truncated?
The question in another form. I actually ran into this agian and forgot about this solution. Took me a bit to remember as I forgot the root cause. Here is what I searched for thinking that SQL Server was the culprit.
What is SQL Servers Varchar(MAX) maximum length? - If your values are being truncated its probably caused by php non sql server. If you just want to know what the max is, the question is answered here: Maximum size of a varchar(max) variable
this was not the case – what actually happened was my string of VARCHAR (MAX) characters being truncated down to an NVARCHAR (4000)! There is a reason for this and its all to do with Data Type Precedence in this case the NVARCHAR is preceding my VARCHAR unless of course I explicitly convert the NVARCHAR to a VARCHAR.
String or binary data would be truncated SQL Server 2019 String or binary data would be truncated Datetime SQL Server String or binary data would be truncated in SQL Server Stored Procedure This error usually occurs when we try to insert more data into a column than the specified column can store.
We don't truncate data at the database level with SQL Server. Tell them it is not possible and that they simply have to truncate it at the application layer. Tell them SQL Server has these safeguards in place to ensure data integrity.
THANKS for your response. We are migrating an old application from Sybase ASE 12.5 to SQL Server 2008 R2. Sybase does this automatic truncate..so, if the users inserts more than 245 characters, it is automatically truncated and stored in the DB. Users want the same behaviour in SQL Server as well..
Answering my own question. Just posting it here because it took me over an hour to hunt this issue down and it would be nice to have a strait-forward answer to it. I've seen similar questions on stack overflow but they were very specific to a use case or so poorly worded they were not findable by search results
mssql module for php has several default settings in the php.ini file. One of those settings is mssql.textlimit
and mssql.textsize
. These settings will truncate any text based field (varchar, text, nvarchar) to the size they are set too.
I have seen posts where the truncate is on 8192 and 4096. So I assume either of these can be default. I upped mine to 65535 (2^16 - 1).
I recently started migrating to PDO and found the mssql direct answer does not work with pdo.
PDO seems to have a bug (not a bug but kind of a bug) that hard-codes the textlimit to 64 kb. You can override this by passing the following query right before your long query: SET TEXTSIZE -1
.
As a side note I have also seen similar question specifically for query results in SQL Server Management Studio. There are several limitations in the program for how many characters can be displayed or exported. Also there are settings in the program to edit the number of characters displayed/exported. More info on this issue here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With