T-SQL long dynamic query gets cut off

Question

I am working with a query that will produce a html page. In the beginning that page is just a simple HTML table with tags. The tags will later be processed and replaced with subqueries (mostly scalar functions). This way combined HTML with replaced tags will be executed with sp_executesql to produce final html.

Excerpt of that HTML:

...Arial, Helvetica, Verdana, sans-serif; font-weight: bold;">Firma:</td><td style="font-size: 14px; font-family: Arial, Helvetica, Verdana, sans-serif;">'+(select cast(coalesce(Companyname,'') as nvarchar(max)) as result from Customer WHERE CustomerID=988082)+'</td></tr><tr><td style="font-size: 14px; font-family: Arial, Helvetica, Verdana, sans-serif; font-weight: bold;">Anrede:</td><td style="font-size: 14px; font-family: Arial, Helvetica, Verdana, sans-serif;">'+(select cast(coalesce(Anrede,'') as nvarchar(max)) as result from Customer WHERE CustomerID=988082)+'</td></tr><tr><td style="font-size: 14px; font-family: Arial, Helvetica, Verdana, sans-serif; font-weight: bold;">Vorname:</td><td style="font-size: 14px; font-family: Arial, Helvetica, Verdana, sans-serif;">'+(select cast(coalesce(Firstname,'') as nvarchar(max)) as result from Customer WHERE CustomerID=988082)+'</td></tr><tr><td style="font-size: 14px; font-family: Arial, Helvetica, Verdana, sans-serif; font-weight: bold;">Nachname:</td><td style="font-size: 14px; font-family: Arial, Helvetica, Verdana, sans-serif;">'+(select cast(coalesce(Lastname,'') as nvarchar(max)) as result from Customer WHERE CustomerID=988082)+'</td></tr><tr><td style="font-size: 1...

Now, I know that if I use anything but nvarchar(max) my result will be truncated to 4000, so I do casting of all function results to nvarchar(max) and my results is more than that but still strangely cut of from inside.

So when I run an example script I get 9043 characters as the result (with len function), and the result is cut off (note: from inside, near the end but not at the end). Now I append one letter and result is 9044. But if I add that letter 100 characters before the end result is still 9043!?

What is wrong with this? Why I can;t build a long T-SQL query to be executed with the expected results?

Thanks

UPDATE 1

Because the original solution is too long to display it here I will try to display smaller version just for you to see how I did it and what is my final doing with it:

declare @BodyTXT nvarchar(max)
set @BodyTXT =  N'select ''some string' + (select cast('result of some function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max))) +
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another AAA string' + (select cast('result of antoher function' as nvarchar(max))) + N''''

execute sp_executesql @BodyTXT

Let's say that this is LEN = 9043 characters long and now, notice AAA if I add characters there the final results stays to be LEN = 9043, but if I add some strings at the very beginning string size will change and increase for the number of strings I add there.

Result will be cut off this way:

some stringresult of some function another stringresult of antoher function
 another stringresult of antoher function another stringresult of antoher 
function another stringresult of antoher function another stringresult of antoher
 function another stringresult of antoher function another ...ult of antoher function

Can you see those ... dots almost at the end of the result it is there where I am missing rest of string that should be in the output (approx 12000, and I am having 9043)

UPDATE 2

Because this all is part of automated emailing system and it was running live I had to find solution and what I have done is removing some style information from the result table. So after cutting 1/3 of html I finally got result as expected BUT because size of the final string depends on how many products customer purchases for certainly this issue will come again. And also I want to know what is the reason for this behavior and how to overcome it.

`I know that if I use anything but nvarchar(max) my result will be truncated to 4000` - that is not really true, it will be truncated if the concatenation expression does *not* contain an `nvarchar(max)` on the right side. It is too late to cast results of functions to `nvarchar(max)`, you need to make sure you cast inside the functions. — GSerg, Dec 28 '18 at 21:32
@GSerg that will be quite a task, I think some 10 functions that are used all over DB and that usually return not more than few characters - to be changed to return nvarchar(max). But do you know why I am still getting 9000 characters in return anyway? I mean this number confuses me...Thanks — Dejan Dozet, Dec 28 '18 at 22:13
How are you viewing the output? Try setting the query output to a text file and see of the text file is the right size. There is some limit on the number of characters in the output window in SSMS — Sparky, Dec 28 '18 at 22:20
@scsimon yes I was using len, but there is no spaces at the end, also I can add more characters and they will be swallowed by something. I think what gserg said makes sense, but now I want to know why >9000 why not 4000 (in that case)? — Dejan Dozet, Dec 28 '18 at 22:27
Wait what. _I am working with a query that will produce a html page_? This absolutely wrong unless it's for an email for reporting purpose. — Evaldas Buinauskas, Dec 28 '18 at 22:36
We need to see the code in the function and how you call it. Did DATALENGTH return the same ? — S3S, Dec 28 '18 at 23:40
`But if I add that letter 100 characters before the end result is still 9043` - how are you doing that? `do you know why I am still getting 9000 characters in return anyway?` - because in the final concatenation you do cast to `nvarchar(max)`, but somewhere in the middle one of the sub-functions returns a piece that was supposed to be longer than 4000 but was truncated because it was not cast to `nvarchar(max)` from inside? — GSerg, Dec 29 '18 at 09:36
@HABO, I don;t know if I understand, but the original html is stored into a table as html with tags in it (placeholders) and those tags are to be replaced with some simple queries and executed as one (huge string) to return final result and send as email — Dejan Dozet, Dec 29 '18 at 10:24
@GSerg, your point it the most logical from them all, and I think that there must be something with how SQL server reserves memory for that final string and that is why it get shortened before getting to the end i will update my question now for more details — Dejan Dozet, Dec 29 '18 at 10:28
So go to the function that returns `another ...ult of antoher function` and fix the specific concatenation expression there that causes truncation. — GSerg, Dec 29 '18 at 11:02
@DejanDozet Yup, I've done similar things with _snippets_ that get replaced on-the-fly, e.g. `'{Username} last login was {LastLoginTime}'`. If a username happens to be "Oops" what would you want displayed on a web page? With HTML encoding users will see a weird looking name, without encoding they will get a link to somewhere they might not want to go. — HABO, Dec 29 '18 at 16:28

score 1 · Answer 1 · answered Dec 29 '18 at 10:34

1

I once have encountered an issue using += operator to concatenate long strings, it was cutting characters down.

So instead using @SQL = @SQL + N'a really long text'; has solved it.

answered Dec 29 '18 at 10:34

Evaldas Buinauskas

13,739
11
55
107

I have a something like a template string with placeholder which are to be replaced with subqueries and executed to produce final output, please take a look at my question (update 2 maybe) – Dejan Dozet Dec 29 '18 at 19:35
1

You are my hero! Thanks for sharing this mate. – Mustafa Erdogan Dec 28 '20 at 14:52

score 1 · Answer 2 · answered Apr 29 '21 at 19:58

I know this is an old Post but still a current subject. This is just as an FYI, to help anyone that would need help on this subject.

I just ran into a problem where my @SqlStr (that is set to NVARCHAR(MAX)) kept on cutting off causing errors during the SP_ExecuteSQL call.

The only concatenation that is done to this long script was @TableName which was set to NVARCHAR(100).

After several hours of debugging, the issue was caused by the @tablename

it seems like when nvarchar(###) is used instead of NVarchar(max), SQL sees it as a slightly different datatype and cuts the long string to 4000 characters.

To fix the issue I had to change the @Tablename to match the nvarchar(max) data type.

well, nowadays I don't think much, just convert everything to nvarchar(max) — Dejan Dozet, Apr 30 '21 at 06:45

score 0 · Answer 3 · answered Dec 29 '18 at 04:35

I recall something similar while generating large html output as a string. Can't recall what exactly was the issue, probably some limitation of management studio. The workaround was to generate table as xml. Below is a sample you can experiment with. Generate sql table first and then replace "select * from (values ..)" with your table.

declare
 @baseTable xml = (select * from ( Values
   (123, 'Firma:', 'Google'),
   (123, 'Anrede:', 'NYC'),
   (123, 'Vorname:', 'John'),
   (123, 'Nachname:', 'Doe')
  ) T(CustID, property, Val)
 for XML PATH('row'), ELEMENTS XSINIL)

,@tblClass VARCHAR(100)
,@thClass VARCHAR(100) 
,@TRstyle VARCHAR(100) = 'font-size: 14px'
,@TDstyle VARCHAR(100) = 'font-size: 14px; font-family: Arial, Helvetica, Verdana, sans-serif; font-weight: bold'

 SELECT @tblClass AS [@class]  
    ,@thClass AS [thead/@class]
    ,@baseTable.query(
              N'let $first:=/row[1]
                return 
                <tr> 
                {
                for $th in $first/*
                return <th>{local-name($th)}</th>
                }
                </tr>') AS thead
            ,@baseTable.query(                 
               N'for $tr in /row
                 return 
                 <tr style="{sql:variable("@TRstyle")}">
                  {
                   for $td in $tr/*
                     return <td style="{sql:variable("@TDstyle")}">{string($td)}</td>
                  }
                 </tr>') AS tbody
    FOR XML PATH('table'),TYPE

String that I use for that I sort of template that users create with wyswyg editor, in behind I have to get that string and replace those tags with subqueries, are you saying I should use xml datatype for this? — Dejan Dozet, Dec 29 '18 at 05:46
Mike, the original post is to be [found here](https://stackoverflow.com/a/39487565/5089204). @DejanDozet, using XML to create (XHTML) is indeed a good idea. The lengt is (almost) unlimited and you get character escaping and validation *for free*. You might check the linked answer to find some working examples. — Shnugo, Dec 29 '18 at 13:47
@dejan-dozet If you are generating HTML output from tables/string built in xml tools might be very useful. At least you don't have to worry about sizes and types. Based on your original post I assumed you creating an html table from some data in your tables. Now I'm not quite sure what is the user input and desired output though. Do users copy/pasting some html stuff you need to transform? Can you provide a sample of actual input/output? — Mike Twc, Dec 29 '18 at 17:37
@MikeTwc, Hi Mike, users are building templates, they are using placeholders that will later be replaced by subqueries like (select dbo.some_function(1246)), so when executed with sp_executesql it will provide final result / string. Is UPDATE 2 enough explanatory to you? I mean actual input is related to real tables, so you can;t execute it anyway, thanks...Also I wonder how I can use XML for this purpose? — Dejan Dozet, Dec 29 '18 at 19:32

score 0 · Answer 4 · answered Jan 02 '19 at 05:50

The problem is almost certainly that your @BodyTXT may be nvarchar(max), but something you are putting into it is not. By doing set @BodyTxt = a + b + c + d + ..... , all of the concatenation is being done first, and then stuck into your variable at once. Which is great, until somewhere SQL decides because of type coercion (or something) that the results of the latest bit are nvarchar (default length 4000), not nvarchar(max), and truncates some of it.

You can go through your whole query, checking everything and looking for the problem, but it would probably be simpler to break the concatenation up into bits that will all be small enough, something like this.

declare @BodyTXT nvarchar(max)

set @BodyTXT =  N'select ''some string' + (select cast('result of some function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max)))

set @BodyTXT = @BodyTXT +
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max))) +

set @BodyTXT = @BodyTXT +
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another string' + (select cast('result of antoher function' as nvarchar(max))) + 
N' another AAA string' + (select cast('result of antoher function' as nvarchar(max))) + N''''

I have checked already that all functions in the query are cast-ed to nvarchar(max), but GSerg stated that those functions have to be nvarchar(max) from within themselves and I can;t convert those function just because of that, it doesn't make sense, not in a way that function that return Post Code should only return varchar(5) because it is correct way to do it. Could it be something in SQL Server 2008? — Dejan Dozet, Jan 02 '19 at 09:56

score -1 · Answer 5 · answered Dec 28 '18 at 22:28

-1

LEN will trim trailing spaces, DATALENGTH will not. select len('a '), datalength('a '). So if you insert that letter 100 characters back then the last character is a space. Just use DATALENGTH instead and you should get your expected results.

answered Dec 28 '18 at 22:28

S3S

24,809
5
26
45

2

The OP is casting as `NVarChar`. From [`Len()`](https://docs.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql): "Returns the number of characters of the specified string expression, **excluding trailing blanks**." [`DataLength`](https://docs.microsoft.com/en-us/sql/t-sql/functions/datalength-transact-sql) does not exclude trailing blanks. For Unicode strings you can use `DataLength( UnicodeStringExpression ) / DataLength( N'#' )` to get the length in characters. In general `DataLength( Left( Coalesce( StringExpression, '#' ), 1 ) )` will return the number of bytes per character. – HABO Dec 29 '18 at 01:22
I agree @HABO and that’s what I stated in my answer. I didn’t know where they were casting but I added documentation to the LEN trimming. – S3S Dec 29 '18 at 01:26
But I only use len to demonstrate to you that I have problem getting full length string as a result. But OK I will post datalength results now – Dejan Dozet Dec 29 '18 at 05:51

T-SQL long dynamic query gets cut off

5 Answers5