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
