Search code examples
.netsql-server-2008.net-assemblyclrstoredprocedure

CLR Integration to return resultset

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.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendReceiveAsXML(SqlString request)
{
    SqlPipe sp;
    sp = SqlContext.Pipe;
    MsgWS obj = new MsgWS();
    string s = obj.SendAndReceiveAsXml(request.ToString());

    sp.Send(s);

}

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.

Solution

  • 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);

sp.Send(row);