如何增加 MSSQL 服务器中用户定义函数溢出的任何缓冲区

Question

试图拉回一大组数据来填充 JQGrid 表的选择框过滤器，我遇到了这种溢出。

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "FormDelimitedString": 
System.Data.SqlTypes.SqlTypeException: The buffer is insufficient. Read or write operation failed.
System.Data.SqlTypes.SqlTypeException: 
   at System.Data.SqlTypes.SqlBytes.Write(Int64 offset, Byte[] buffer, Int32 offsetInBuffer, Int32 count)
   at System.Data.SqlTypes.StreamOnSqlBytes.Write(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BinaryWriter.Write(String value)
   at SqlServerProject1.FormDelimitedString.Write(BinaryWriter w)

以下是 SQL：

 SELECT 
  dbo.formDelimitedString (DISTINCT v_mie_all.AREA) AS AREA, 
  dbo.formDelimitedString (DISTINCT v_mie_all.PROP_TYPE) AS PROP_TYPE, 
  dbo.formDelimitedString (DISTINCT ((SUBSTRING (v_mie_all.rp_code, 1, 4)))) AS REC_TYPE_USE_CODE, 
  dbo.formDelimitedString (DISTINCT v_mie_all.TRANS_CODE) AS TRANS_CODE, 
  --dbo.formDelimitedString (DISTINCT v_mie_all.MI_FUNDS_MGR) AS MI_FUNDS_MGR,
  dbo.formDelimitedString (DISTINCT v_mie_all.EQ_FUNDS_MGR) AS EQ_FUNDS_MGR, 
  dbo.formDelimitedString (DISTINCT v_mie_all.CNTRCT_ARNGMT) AS CNTRCT_ARNGMT,
  dbo.formDelimitedString (DISTINCT v_mie_all.SU_ID) AS SU_ID
FROM v_mie_all 

以下是该函数的来源：

CREATE AGGREGATE [dbo].[FormDelimitedString]
(@value [nvarchar](4000))
RETURNS[nvarchar](4000)
EXTERNAL NAME [FormDelimitedString].[SqlServerProject1.FormDelimitedString]
GO

嗯，也许我可以将 nvarchar4000 增加到更大的值。

Answer 1

我现在正在使用这个

SELECT Stuff((SELECT DISTINCT '|' + CAST(MY_COLUMN_NAME AS nvarchar)
 FROM   MY_TABLE_NAME 
 FOR xml path, type).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS MY_COLUMN_NAME,

旧代码如下所示：

SELECT dbo.formDelimitedString (DISTINCT MY_COLUMN_NAME) AS MY_COLUMN_NAME,