September 14, 2009 at 12:45 pm
I've created a simple CLR to save a properly formatted FOR XML result set to a file on my SQL 2005 Server. I now need to send that file to an external hosted Plain Old XML (POX) service and read the results back in order to send an e-mail notification in case of failure.
September 15, 2009 at 2:47 pm
Hi Michael
If your xml is not too large you can load it into a MemoryStream and use a HttpRequest to send it to your server. In other case you have to use a in/out streaming.
Here a simple sample.
[Microsoft.SqlServer.Server.SqlProcedure]
public static SqlInt32 StoredProcedure1( )
{
// the statement
string sql = "SELECT TOP(10) * FROM INFORMATION_SCHEMA.COLUMNS FOR XML AUTO, ROOT('Root')";
using (SqlConnection cn = new SqlConnection("Context Connection=true"))
//using (SqlConnection cn = new SqlConnection("Server=.;Database=Sandbox;Trusted_Connection=true;"))
{
// open connection
cn.Open();
// init command, xml reader, xml writer and a memory stream
using (SqlCommand cmd = new SqlCommand(sql, cn))
using (XmlReader xmlReader = cmd.ExecuteXmlReader())
using (MemoryStream memStream = new MemoryStream())
using (XmlWriter xmlWriter = XmlWriter.Create(memStream))
{
// write the returned xml into a memory stream
xmlWriter.WriteNode(xmlReader, true);
xmlWriter.Flush();
// set position back in memory stream to enable reading data
memStream.Position = 0;
// create a web request
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create("http://tempuri.org");
// set content length BEFORE you start writing the content stream
request.ContentLength = memStream.Length;
// Get the content stream and writ the xml into
Stream requestStream = request.GetRequestStream();
byte[] buffer = new byte[1024];
int length;
while (0 != (length = memStream.Read(buffer, 0, buffer.Length)))
{
requestStream.Write(buffer, 0, length);
}
// Get the response stream and do whatever you want
WebResponse response = request.GetResponse();
Stream responseStream = response.GetResponseStream();
}
}
return new SqlInt32(0);
}
Greets
Flo
September 17, 2009 at 2:19 pm
Thanks Flo,
Using almost the exact c# you provided, changing only the destination and sql connect and for xml query,
Here is what I am now getting as a response from SQL:
Msg 6522, Level 16, State 1, Procedure PostAgents, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "PostAgents":
System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint)
at System.Net.HttpRequestCreator.Create(Uri Uri)
at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase)
at System.Net.WebRequest.Create(String requestUriString)
at StoredProcedures.PostAgents()
.
September 17, 2009 at 3:11 pm
UPDATE: After setting my Assembly to External Access from Safe, I now get the following error:
The objective is to send the results of a "FOR XML" query to a POX (Plain Old XML) Host.
Msg 6522, Level 16, State 1, Procedure PostAgents, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "PostAgents":
System.Net.ProtocolViolationException: Cannot send a content-body with this verb-type.
System.Net.ProtocolViolationException:
at System.Net.HttpWebRequest.CheckProtocol(Boolean onRequestStream)
at System.Net.HttpWebRequest.GetRequestStream()
at StoredProcedures.PostAgents()
Thank you for your help..
September 17, 2009 at 8:20 pm
At this point, the error does not appear to have anything to do with SQLCLR.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 18, 2009 at 1:59 am
Hi Michael
Seems you have to change your Request.Method from "GET" to "POST"
http://social.msdn.microsoft.com/Forums/en-US/vststest/thread/02dd800a-a9cf-4dca-bb55-16b5672e12ff[/url]
Greets
Flo
September 18, 2009 at 12:36 pm
Finally got it to work. What I had to do was put in the ContentType = 'text/xml'
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PostXMLTransaction(string v_strURL, string v_strFILE)
{
// the statement
using (XmlTextReader xmlReader = new XmlTextReader (v_strFILE))
// init command, xml reader, xml writer and a memory stream
// using (XmlReader xmlReader = cmd.ExecuteXmlReader())
using (MemoryStream memStream = new MemoryStream())
using (XmlWriter xmlWriter = XmlWriter.Create(memStream))
{
// write the returned xml into a memory stream
xmlWriter.WriteNode(xmlReader, true);
xmlWriter.Flush();
// set position back in memory stream to enable reading data
memStream.Position = 0;
// create a web request
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(v_strURL);
// set content length BEFORE you start writing the content stream
request.Method = "POST";
request.ContentLength = memStream.Length;
request.ContentType = "text/xml";
// Get the content stream and writ the xml into
Stream requestStream = request.GetRequestStream();
byte[] buffer = new byte[1024];
int length;
while (0 != (length = memStream.Read(buffer, 0, buffer.Length)))
{
requestStream.Write(buffer, 0, length);
}
// Get the response stream and do whatever you want
WebResponse response = request.GetResponse();
Stream responseStream = response.GetResponseStream();
}
}
Thank you for your help
September 18, 2009 at 12:48 pm
Dang! Forgot that, sorry.
Glad we could help! 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply