Take the 2-minute tour ×

I am integrating CLR with SQL Server 2008, it's successfully configured. but i am calling a webservice inside this SqlProcedure.

Here is the code, i have tried to return xml response.

public static void SendReceiveAsXML(SqlString request)
    SqlPipe sp;
    sp = SqlContext.Pipe;
    MsgWS obj = new MsgWS();
    string s = obj.SendAndReceiveAsXml(request.ToString());



There may be a possibility of "maximum length exceeds exception". to prevent this, i need to retrieve the response as reader as well as this is the requirement of end component i am integrating with sql.

enter image description here

Any suggestions are welcome.

share|improve this question
What line does the exception occur in? Is it in sp.Send(s);? –  LuigiEdlCarno Oct 9 '12 at 7:31
@LuigiEdlCarno please check the updated question. –  Sujit Oct 9 '12 at 7:33

1 Answer 1

up vote 2 down vote accepted

Pipe.Send(string) is intended for returning messages back to the client. For such a large piece of text, you should probably create an SqlDataRecord, to return this to the client as a result set - where you can effectively return it as a varchar(max) column.

So, you'd edit the end of your method to this:

string s = obj.SendAndReceiveAsXml(request.ToString());

var md = new SqlMetaData("ColumnA", SqlDbType.VarChar, -1);
var row = new SqlDataRecord(md);
row.SetString(0, s);

share|improve this answer
do SqlDataRecord support multiple record set? because if i convert the response to dataset, it returns me multiple recordset. any suggestion. –  Sujit Oct 9 '12 at 7:42
@asdasd - Why are you converting it to a dataset? If you already have it as a string, you can store that as a single column in a single row, provided the column is varchar(max). –  Damien_The_Unbeliever Oct 9 '12 at 7:47
actually, this is the requirement for other application to accept the output as datatable from sql. –  Sujit Oct 9 '12 at 7:52
Pipe.Send(SqlDataRecord) sends a single row result set - the consuming code will see it as a data table. –  Damien_The_Unbeliever Oct 9 '12 at 7:56
thanks, but how do i get the recordset from string data? –  Sujit Oct 9 '12 at 8:18

Your Answer


