Using VARCHAR(MAX) with string concatenation in SQL Server 2005

Question

A user of one of our databases is trying to submit an UPDATE query to a SQL Server 2005 database and the text is being truncated unexpectedly.

The field being truncated is VARBINARY(MAX) and is used to store HTML text.

The query is roughly:

UPDATE Story 
SET mainText = CONVERT (VARBINARY (MAX), '[...5000 chars of text...]' 
    + char(47) + char(47)
    + '[...3000 chars of text...]'
    + char(47) + char(47)
    + '[...5000 chars of text...]') 
WHERE storyId = 123456

What I found after some experimentation is that when I remove the string concatenation, the query works as expected and the field is not truncated.

I was able to work around the limitation and keep the concatenation by wrapping each separate string in a CAST to VARCHAR(MAX), so there is an option if the user feels the use of char() is required.

What I think is going on is, whenever the concatenation operator is used, there's an implicit conversion happening to VARCHAR and that implicit conversion seems to be limited to VARCHAR(8000) instead of VARCHAR(MAX). So, before the string is even sent to the CONVERT function, it's already being truncated down to 8000 characters.

If I'm correct, is there a way to change that behavior?

If there is no way to change the behavior, is there another way to deal with the issue besides CAST?

Answer 1

You need to CAST the first string first.

CONVERT (VARBINARY (MAX), '[...5000 chars of text...]')
    + char(47) + char(47)
    + '[...3000 chars of text...]'
    + char(47) + char(47)
    + '[...5000 chars of text...]'

Before, the inner text never goes more then 8000 bytes. Then you are CASting. Too late.

'[...5000 chars of text...]') 
    + char(47) + char(47)
    + '[...3000 chars of text...]'
    + char(47) + char(47)
    + '[...5000 chars of text...]' 

For a more complete walkthrough of "why", please see my answer here "For Nvarchar(Max) I am only getting 4000 characters in TSQL?"