SQL Server NVARCHAR(MAX) String Not Printing All Characters

database-mailsql serversql-server-2008stored-procedures

I am writing a stored procedure to use the sp_send_dbmail system stored procedure to send an HTML email. I have used this procedure before but have not run into this problem before. I am using the build method laid out here

From online I believe that NVARCHAR(MAX) should hold a maximum of 2 147 483 647 characters (ref)

However, when I build my output if I PRINT the output it does not return the full string.

I have tested my SQL separately and this returns as expected.

Clearly I have a mistake but can someone point what it is please!

The script is:

Declare @RawPart varchar(30);
Declare @PO varchar(30);
Declare @NL varchar(12)='<br/>';
DECLARE @BodyHTML  NVARCHAR(MAX) ;
Declare @SubjectText varchar(200);
Declare @StaffEmail varchar(50);
Declare @MrpDate varchar(12);


Set @MrpDate=(Select convert (varchar(12),[SnapshotDate],103) from [dbo].[MrpReqCtl] )

set @StaffEmail='SOMEPLACE@DOMAIN.co.uk';
set @SubjectText ='Schedule Update from MRP Email';
Set @PO='106277';
Set @RawPart=(Select Distinct  MStockCode From dbo.PorMasterDetail Where PurchaseOrder=@PO and LineType=1)


--N'<p>Attention From the Mrp run '+@MrpDate+N'</p><p>Has Detected Changed to the following Schedules</>'
----Define Customer Schedule Table
--         +
Set @BodyHTML=      N'<H1>Customer Schedules</H1>' +
                    N'<table border="1">' +
                    N'<tr><th>Sales Order</th><th>Line</th><th>Ship Date</th><th>Customer</th><th>Stockcode</th><th>Os Qty</th><th>Status</th>'

--Stage 2 Customer Order Details Table
                     +cast( (Select SalesOrder, SalesOrderLine, convert(varchar(12),MLineShipDate,103) as DispatchDate
                                , Customer,rtrim(StockCode)+' - '+ rtrim(F.StockCodeDesc) ,cast(OutstandingQty as int) OutstandingQty
                                ,Case When MLineShipDate<datediff(d,0,getdate())  Then 'Arrs' else '' end as Status 
                                From [dbo].[CHCIW_ForwardOrders] F
                                Where F.StockCode IN (
                                                        Select BC.TopLevel
                                                        FROM         dbo.K3_vwBOMCosting BC
                                                        inner Join dbo.InvMaster I On BC.StockCode=I.StockCode
                                                        Where [TopLevel] like 'MG%' and I.StockCode=@RawPart )
                                        and MLineShipDate<dateadd(m,3,datediff(d,0,getdate()) )
                                Order By MLineShipDate
                                For xml Path('tr'), Type) as nvarchar(max))+  N'</table>' ;
Set @BodyHTML=rtrim(@BodyHTML)

Print len(@BodyHTML)
Print @BodyHTML

However, the output is:

6326

<H1>Customer Schedules</H1><table border="1"><tr><th>Sales Order</th><th>Line</th><th>Ship Date</th><th>Customer</th><th>Stockcode</th><th>Os Qty</th><th>Status</th><tr><SalesOrder>010879</SalesOrder><SalesOrderLine>5</SalesOrderLine><DispatchDate>30/10/2017</DispatchDate><Customer>BORG02</Customer>MG16311504501 - VW 1.0L EU6ZD Machined -1565<OutstandingQty>249</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>177</SalesOrderLine><DispatchDate>10/11/2017</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>846</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010879</SalesOrder><SalesOrderLine>7</SalesOrderLine><DispatchDate>10/11/2017</DispatchDate><Customer>BORG02</Customer>MG16311504501 - VW 1.0L EU6ZD Machined -1565<OutstandingQty>468</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>179</SalesOrderLine><DispatchDate>17/11/2017</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>7020</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010879</SalesOrder><SalesOrderLine>9</SalesOrderLine><DispatchDate>17/11/2017</DispatchDate><Customer>BORG02</Customer>MG16311504501 - VW 1.0L EU6ZD Machined -1565<OutstandingQty>468</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>181</SalesOrderLine><DispatchDate>24/11/2017</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>7020</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010879</SalesOrder><SalesOrderLine>11</SalesOrderLine><DispatchDate>30/11/2017</DispatchDate><Customer>BORG02</Customer>MG16311504501 - VW 1.0L EU6ZD Machined -1565<OutstandingQty>720</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>183</SalesOrderLine><DispatchDate>01/12/2017</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>7020</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010879</SalesOrder><SalesOrderLine>41</SalesOrderLine><DispatchDate>07/12/2017</DispatchDate><Customer>BORG02</Customer>MG16311504501 - VW 1.0L EU6ZD Machined -1565<OutstandingQty>1224</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>185</SalesOrderLine><DispatchDate>08/12/2017</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>7020</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010879</SalesOrder><SalesOrderLine>43</SalesOrderLine><DispatchDate>14/12/2017</DispatchDate><Customer>BORG02</Customer>MG16311504501 - VW 1.0L EU6ZD Machined -1565<OutstandingQty>252</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>187</SalesOrderLine><DispatchDate>15/12/2017</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>7020</OutstandingQty><Status>Arrs</Status></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>189</SalesOrderLine><DispatchDate>22/12/2017</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>7020</OutstandingQty><Status/></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>191</SalesOrderLine><DispatchDate>29/12/2017</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>10920</OutstandingQty><Status/></tr><tr><SalesOrder>010879</SalesOrder><SalesOrderLine>45</SalesOrderLine><DispatchDate>04/01/2018</DispatchDate><Customer>BORG02</Customer>MG16311504501 - VW 1.0L EU6ZD Machined -1565<OutstandingQty>252</OutstandingQty><Status/></tr><tr><SalesOrder>010158</SalesOrder><SalesOrderLine>193</SalesOrderLine><DispatchDate>05/01/2018</DispatchDate><Customer>BORG02</Customer>XXXXXXXXX507 - SOME DECRIPTIVE TEXT XXXXX<OutstandingQty>10920</OutstandingQty><Status/

Best Answer

This is a limitation of what can be displayed in the "Messages" tab via either PRINT or RAISERROR. They can either show 4000 characters of NVARCHAR data or 8000 character of VARCHAR data.

There are a few ways around this, such as breaking the string into chunks that will work within those limits via a CURSOR. But, if:

  • you have less than 8001 characters (which you do), AND
  • you do not have any characters in that string that won't fit into the Code Page specified by the Database's default Collation

then you could just convert the string to VARCHAR(8000):

PRINT CONVERT(VARCHAR(8000), @BodyHTML);

For example:

DECLARE @String NVARCHAR(MAX) = N'A'
    + REPLICATE(CONVERT(NVARCHAR(MAX), N'_'), 7998)
    + N'Z123';

PRINT CONVERT(VARCHAR(MAX), @String);

If you execute that and then check the "Messages" tab, you should have a line that starts with "A___", then plenty more "_"s, and then ends with "___Z". You should not see the "123" (as those are characters 8001 - 8003). If you place your cursor at the end of that line (just to the right of the "Z"), then the "Col" and "Ch" values in the blue bar at the bottom of SSMS should both be 8001.

P.S. Using either VARCHAR(8000) or VARCHAR(MAX) should be fine. I haven't seen a difference in behavior between them for this particular scenario.

Also, to clarify the datatype limit: 2,147,483,647 is the maximum number of bytes that can be stored in an NVARCHAR(MAX) column. Since NVARCHAR is UTF-16 data, it uses either 2 or 4 bytes per each "character", with most typically used characters falling in the 2-byte group. Meaning, you can get at most HALF of that byte limit as characters, if all characters are of the 2-byte variety. If any characters are the 4-byte variety, then the total number of characters it can hold decreases since the maximum number of bytes does not change.

Also, the documentation is incorrect in that it says that "2,147,483,647 is the maximum number of characters". I will submit a correct for that.

Related Question