How do I increase whichever buffer is overflowing for a user defined function in MSSQL Server?

Question

Trying to pull back a large set of data to populate select box filters for a JQGrid table and I'm running into this overflow.

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)

Here is the 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

Here is the source for the function:

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

Hmm maybe I can just increase nvarchar4000 to something bigger.

The problem is quite possibly in your .Net code. Regardless here's a method to do that purely in T-SQL which means you won't have to mess with CLR http://stackoverflow.com/questions/1048209/concatenating-column-values-into-a-comma-separated-list — Nick.Mc, Feb 04 '16 at 03:36

score 0 · Accepted Answer · answered Feb 10 '16 at 05:04

I'm now using this instead

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,

The old code looked like:

SELECT dbo.formDelimitedString (DISTINCT MY_COLUMN_NAME) AS MY_COLUMN_NAME,

How do I increase whichever buffer is overflowing for a user defined function in MSSQL Server?

1 Answers1