3

Just ran into a major headache when concatenating several @varchar(max) variables together to build an email based on several different queries.

For efficiencies sake, I was using several varchars to build the email at once, rather than going through roughly the same query two or three or more times to build it using only one varchar.

This worked, right up until my varchars got to longer than 8000 characters. Then the concatenation of them all into one varchar (which I could shove into the @body parameter of msdb.dbo.sp_send_dbmail) returned "", and even LEN() wouldn't actually give me a length.

Anyhow, I've gotten around this by doing roughly the same queries several times and building the email with only one varchar(max).

TL;DR

I'm not happy with the solution. How could I have appended these varchar(max) variables to each other?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dakine83
  • 687
  • 2
  • 9
  • 23

3 Answers3

5

One thing I've hit in the past which may or may not help here: SQL seems to "forget" what datatype its working with when you concatenate varchar(max). Instead of maintaining the MAX, it devolves to conventional varcharnitude, meaning truncation at 8000 characters or so. To get around this, we use the following trick:

Start with 

SET @MyMaxVarchar = @aVarcharMaxValue + @SomeString + @SomeOtherString + @etc

and revise like so:

SET @MyMaxVarchar = cast(@aVarcharMaxValue as varchar(max)) + @SomeString + @SomeOtherString + @etc

Again, this may not help with your particular problem, but remembering it might save you major headaches down the road some day.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Philip, have you ever encountered a situation where your fix works in one database but not another? I'm testing on the same server, in an old mirrored database, so the records are behind by a month or so, but the structure is the exact same. In my test area, your fix works flawlessly. In live, it fails after about 9600 characters. That makes me think it's NOT the varchar(max) thing, but I thought I'd ask. – Dakine83 Jan 27 '12 at 18:32
  • 1
    As you might well guess, I've had any number of problems along these lines, but your current one (9600 characters?) doesn't ring any bells. More often than not, some completely obscure pointless detail or overlooked configuration setting is to blame. – Philip Kelley Jan 27 '12 at 19:17
  • You are correct. Somehow a null value got into the DB that wasn't supposed to be there. One more problem to fix! Thanks for all the assistance. – Dakine83 Jan 27 '12 at 23:00
0

This may not have happened in your case, but there's a "gotcha" embedded in SQL Management Studio involving VARCHAR(MAX): SQL Studio will only output so many characters in the results grid. You can test this:

SELECT @MyLongVar, LEN(@MyLongVar)

You may find that the length of the actual data returned (most text editors can give you this) is less than the length of the data stored in the variable.

The fix is in Tools | Options | Query Results | SQL Server | Results to Grid; increase Maximum Characters Retrieved | Non XML data to some very large number. Unfortunately the maximum is 65,535, which may not be enough.

If your problem does not involve outputting the variable's value in SQL Studio, please disregard.

0

I have found that MS SQL silently does NOTHING when attempting to concatentate a string to a NULL value. therefore this solution always works for me:

UPDATE myTable
   SET isNull(myCol, '') += 'my text'
 WHERE myColumnID = 9999
Garry Harstad
  • 1