1

I have a varchar(max) defined but it is truncating the string.

I have searched for the solution and found that all the new values assigned or appended to the string should be cast to varchar(max) but it still didn't help.

Here is my code:

DECLARE @tr VARCHAR(MAX) = ''

SELECT
    @tr = CAST(CAST(@tr AS VARCHAR(MAX)) + 
               CAST(ISNULL(CONCAT
                (
                    '<tr>',
                    '<td class="dataline">',t.ComponentEquipmentNumber,'</td>',
                    '<td class="dataline">',t.ComponentDescription,'</td>',
                    '<td class="dataline">',t.ReceiverCustodian,'</td>',
                    '<td class="dataline">',t.[Location],'</td>',
                    '<td class="dataline">',t.ParentSystemNumber,'</td>',
                    '<td class="dataline">',t.ParentSystemLocation,'</td>',
                    '<td class="dataline">',t.RootSystemNumber,'</td>',
                    '<td class="dataline">',t.RootSystemLocation,'</td>',
                    '<tr/>'
                ),'') AS VARCHAR(MAX)) AS VARCHAR(MAX))
FROM 
    #TempFinal t 
ORDER BY 
    EquipmentId

You can see I have put cast to VARCHAR(MAX) on all levels just to make it works, but it still didn't work.

And when I try to print the length of the string print len(@tr), it returns this strange number 76788 which is not even equal to 8000 characters of VARCHAR(MAX), not getting the logic of this number.

Please help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pawan Nogariya
  • 8,330
  • 12
  • 52
  • 105
  • 2
    That strange number is the length of the final string, and if it's greater than 8000 that means the `VARCHAR(MAX)` is doing its job (the maximum length of that is 2^32 - 1 characters, not 8000). How are you establishing that the string is truncated? Specfically printing it or displaying it in SSMS may truncate it there while the actual value is correct. – Jeroen Mostert Jul 19 '19 at 11:44
  • 1
    probably is only truncating in the results window – Rubens Farias Jul 19 '19 at 11:44
  • 2
    `PRINT` will only print up to 4,000 characters and the maximum characters in a single "cell" in the grid view is 65535 characters. If you need more than that, you need to export the data another way. – Thom A Jul 19 '19 at 11:48
  • why you are saving such big data in single `varchar` variable in `sql`. It is not gonig to be saved anywhere, so what is the use of this expression in sql? – DarkRob Jul 19 '19 at 11:56
  • My *guess* is that the OP is going to pass the value to `sp_send_dbmail`, considering they are creating table HTML, @DarkRob . – Thom A Jul 19 '19 at 11:58
  • 1
    @pawan: can you post further code where do you want to use this, there might be some other way so that you don't need to create this string. – DarkRob Jul 19 '19 at 12:08

0 Answers0