5

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/
  • Just a question why is this a duplicate. My datatype was NVARCHAR (Max)? The cause of the problem I can see as the same but I did not use that answer because if the different datatype – Ian W Dec 19 '17 at 19:56
  • Ian: your question is not a duplicate, but not for the reason you are stating. What matters here is the length of the string. Because you have less than 8001 characters, and assuming that they all fit within the Code Page specified by the Database's default Collation, you can use PRINT and do not need to mess with any other mechanism to break the string into 4000 character chunks to print individually. Not sure why this was closed as a dupe. Also, you can either edit a comment within 5 minutes of posting it, or you can add a new one and then delete the original. – Solomon Rutzky Dec 19 '17 at 20:10
9

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.

  • CAST your output to XML and you'll be able to see the full value – Nic Dec 19 '17 at 17:58
  • @Nic Cast to XML for a PRINT statement? The O.P. wants to use PRINT, not SELECT. And if using SELECT, then the string wouldn't have been truncated as it isn't large enough for that. – Solomon Rutzky Dec 19 '17 at 18:01
  • they'll never get the full string using print..makes no sense – Nic Dec 19 '17 at 18:45
  • Thank you for the explanation and the suggested solution. In this case will just send the email. But the work round is good to knon – Ian W Dec 19 '17 at 19:51
  • @Nic regarding your statement of "they'll never get the full string using print": I don't see how not given that it definitely works. I even updated my answer with some example code showing that it works. – Solomon Rutzky Dec 19 '17 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.