NVARCHAR(MAX) 字元串似乎有 6326 個字元，但不會全部列印

我正在編寫一個儲存過程來使用sp_send_dbmail系統儲存過程來發送 HTML 電子郵件。我以前使用過這個程序，但以前沒有遇到過這個問題。我正在使用此處列出的建構方法

從網上我相信NVARCHAR(MAX)應該最多包含 2 147 483 647 個字元（參考）

但是，當我建構輸出時，如果我PRINT輸出它不會返回完整的字元串。

我已經單獨測試了我的 SQL，並且按預期返回。

顯然我有一個錯誤，但有人可以指出它是什麼！

腳本是：

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

但是，輸出是：

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/

這是通過PRINT或可以在“消息”選項卡中顯示的內容的限制RAISERROR。它們可以顯示 4000 個字元的NVARCHAR數據或 8000 個字元的VARCHAR數據。

有幾種方法可以解決這個問題，例如將字元串分解成塊，這些塊將通過CURSOR. 但是，如果：

• 你有少於 8001 個字元（你這樣做），並且
• 您在該字元串中沒有任何字元不適合數據庫預設排序規則指定的程式碼頁

那麼您可以將字元串轉換為VARCHAR(8000)：

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

例如：

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

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

如果你執行它然後檢查“消息”選項卡，你應該有一個以“A___”開頭的行，然後是更多的“_”，然後以“___Z”結束。您不應該看到“123”（因為這些是字元 8001 - 8003）。如果將游標放在該行的末尾（就在“Z”的右側），那麼 SSMS 底部藍色欄中的“Col”和“Ch”值都應該是8001。

PS 使用其中一個VARCHAR(8000)或VARCHAR(MAX)應該沒問題。對於這種特定情況，我沒有看到它們之間的行為差異。

---

此外，為了澄清數據類型限制：2,147,483,647 是可以儲存在列中的最大字節數。NVARCHAR(MAX)由於NVARCHAR是 UTF-16 數據，因此每個“字元”使用 2 或 4 個字節，最常用的字元屬於 2 字節組。意思是，如果所有字元都是 2 字節的種類，您最多可以得到該字節限制的一半作為字元。如果任何字元是 4 字節變體，那麼它可以容納的字元總數會減少，因為最大字節數不會改變。

此外，文件不正確，因為它說“2,147,483,647 是最大字元數”。我會為此送出一個正確的。

引用自：https://dba.stackexchange.com/questions/193546