How to send long string using SqlContext.pipe.send(strString)?

Hi,

I have a string almost 11006 length.. when i try to send back from SQLCLR procedure

it says cannot send ..

here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."

Max limit to send a string using pipe is 4000

How I can send a string which is large in size than 4000.

Thanks

[419 byte] By [Muna] at [2007-12-23]

«« Problem Creating Stored Procedure From A Library, DLL
»» Custom strings erased from resourcefile

# 1

SqlPipe.Send(String) sends back a message, not data. It is limited to 4000 characters because CLR procedures use Unicode. Send is in effect doing a T-SQL print statement which truncates strings that are too long. It meant to be an informational message for the client.

If you are trying to send back a string as data from a CLR stored procedure one thing you could do is to add an output parameter to your stored procedure, for example:

public SqlInt32 MyProc(ref SqlString data)
{
data = "some real long string";
return 0;
}

Then in T-SQL you could call it, for example:

DECLARE @data NVARCHAR(MAX)
EXEC MyProc @data OUTPUT

Dan

Dan1580 at 2007-8-30 >