Post to HTTP With paramerters Options

Hi,

Please could some of the experts out there advise on the best way to acheive the following please. . .

In a SQL table, I have a field that when it drops to below 5, i want to automatically run a Stored Procedure that then connects to a URL to run a ASP.NET script.

IE:
In table users, there is a field called Money.
When Money reaches < 5
Run Stored Procedure
The Stored Procedure then 'somehow' sends a trigger to a URL with
paramerters as per below
www.mydomainname.com?money=4&name=peter

Any help/guidence appriciated as i am really lost on where to start!

Thanks
H

Create a triggered event on your table that does something when the 'money' column changes.

CREATE TRIGGER trgMoneyUpd

ON users

FOR UPDATE

AS

IF UPDATE(Money)

... call your ESP ...

END

Create an extended stored procedure to construct the string you want and open the URL.

http://msdn2.microsoft.com/en-US/library/aa197263(SQL.80).aspx

Hope that helps,

John (MSFT)

Remember to mark your question as answered if it is answered.

|||

Thanks for that John;

Now i have the trigger part working fine, but for the life of me i cannot seem to get my head around the extended stored procedure;

Does anyone have a simple sample they could provide that enables me to do what i want?

Thanks

Harry

|||Hi,

nothing for your solution about the extended stored procedure, but concerning the trigger, you should see that it is able to handle multiple affected rows and no affected rows (a trigger is fired regardless if a row is affected or not) , as the above mentioned one does not do that.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Harry,

The trigger can call into a CLR stored procedure. Inside the CLR stored procedure you would write some .Net Framework code which will call out to the URL. Something like the following:

Code Snippet

using System;
using System.Net;

namespace InSrvWebClient
{
public class MyHttpClient
{
public static String CallWebURL()
{
String retValue = "";
String resText = "";
System.IO.Stream httpReqBodyStream;
System.IO.Stream httpResBodyStream;
System.IO.StreamWriter httpReqBody;
System.IO.StreamReader httpResBody;
System.Text.StringBuilder httpReqString;

httpReqString = new System.Text.StringBuilder(10);

try {
// Create HTTP connection
System.Net.HttpWebRequest httpClient = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(@."http://www.mydomainname.com?money=4&name=peter");

// Set network credentials if needed

/*
String strUserName = @."myUser";

String strUserPwd = @."pwd";
System.Net.NetworkCredential myCred = new System.Net.NetworkCredential(strUserName, strUserPwd);

System.Net.CredentialCache netCreds = new System.Net.CredentialCache();
netCreds.Add(new Uri(@."www.mydomainname.com?money=4&name=peter");

// Set network credentials if needed

/*
String strUserName = @."myUser";

String strUserPwd = @."pwd";
System.Net.NetworkCredential myCred = new System.Net.NetworkCredential(strUserName, strUserPwd);

System.Net.CredentialCache netCreds = new System.Net.CredentialCache();
netCreds.Add(new Uri(@."http://www.mydomainname.com"), "NTLM", myCred);
netCreds.Add(new Uri(@."http://www.mydomainname.com"), "Digest", myCred);
netCreds.Add(new Uri(@."http://www.mydomainname.com"), "Kerberos", myCred);

*/

// Set Request settings
//httpClient.Credentials = netCreds;
//httpClient.ContentType = "text/xml; charset=utf-8";
httpClient.KeepAlive = true;
httpClient.Method = "GET"; // "POST"
//httpClient.PreAuthenticate = true;
httpClient.ProtocolVersion = System.Net.HttpVersion.Version11;
httpClient.Timeout = 120000;

try {
// Get and write request body stream
//httpReqBodyStream = httppClient.GetRequestStream();
//httpReqBody = new System.IO.StreamWriter(httpReqBodyStream, System.Text.Encoding.UTF8);
//httpReqBody.Flush();
//httpReqBody.Write(soapReqString.ToString());
//httpReqBody.Close();

// Get and parse response body stream
System.Net.HttpWebResponse httpRes = (System.Net.HttpWebResponse) httpClient.GetResponse();
if (httpRes.StatusCode == System.Net.HttpStatusCode.OK)
{
httpResBodyStream = httpRes.GetResponseStream();
httpResBody = new System.IO.StreamReader(httpResBodyStream, System.Text.Encoding.UTF8);
resText = httpResBody.ReadToEnd();

// parse any responses as necessary
}
else
retValue = httpRes.StatusCode.ToString() + ": " + httpRes.StatusDescription;
httpRes.Close();
}
catch (ProtocolViolationException protE)
{
retValue = protE.ToString();
}
}
catch (WebException webE)
{
retValue = webE.ToString();
}
return retValue;
}
}
}

You will then need to register the assembly (dll) with SQL Server:

Code Snippet

create assembly testDll from '' WITH permission_set = external_access
go

CREATE FUNCTION InProcWebRequest()
RETURNS nvarchar(4000)
AS EXTERNAL NAME testDll.[InSrvWebClient.MyHttpClient].CallWebURL
go

For more information on creating a CLR stored procedure please see: http://msdn2.microsoft.com/en-us/library/5czye81z(VS.80).aspx

HTH,

Jimmy