主页 / dba / 问题 / 193546
Accepted
Ian W
Asked: 2017-12-20 08:09:58 +0800 CST

NVARCHAR(MAX) 字符串似乎有 6326 个字符，但不会全部打印

  • 772

我正在编写一个存储过程来使用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='[email protected]';
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/
sql-server sql-server-2008

1 个回答

  1. Best Answer

    这是通过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 是最大字符数”。我会为此提交一个正确的。

    • 9

相关问题