When working on code developing a SQLCLR stored procedure for my upcoming presentation at the Richmond Code Camp, I ran into this problem with the SqlPipe output.

Here is my code:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub procExtractSubjectDetail ()
'Return a string of Subject IDs/XML seperated by a ";"
Try
'Connect to database, notice the context connection
Dim Command As SqlCommand = New SqlCommand
Command.Connection = New SqlConnection("Context connection=true")
Command.Connection.Open()

'Generate the SQL query
Dim strSQL As String = "SELECT ID, Study FROM StudyDetail"

'Attach query to command object
Command.CommandText = strSQL

'Execute command object and get results back in a SQL reader object
Dim reader As SqlDataReader = Command.ExecuteReader()

'Create an efficient stringbuilder to hold the results
Dim Subjects As StringBuilder = New StringBuilder

'Add an emtpy string
Subjects.Append("")

'Collect the results
While reader.Read
'String output size limit is 4kb
'so let us restrict our output for this demo
Subjects.Append(reader.Item(0).ToString() + "/")
Dim sXml As SqlXml = reader.GetSqlXml(1)

Subjects.Append(sXml.Value.ToString)
End While

'Close and release the reader
reader.Close()
reader = Nothing

Dim strResult As String = Subjects.ToString()

'Is it too big?
Dim iSize As Integer = strResult.Length

'Send the results
SqlContext.Pipe.Send(strResult)

Command.Connection.Close()

Catch ex As Exception
'Catch the error and resend it. You could add more error code handling here.
Throw New ApplicationException("An error occurred. " + ex.ToString())
End Try

End Sub

--

This was failing when I ran the code. The issue? SqlContext.Pipe.Send(STRING) only allows a string size of 4k bytes. My output was over 7k.