Hey guys, I want to ping a url over http from a sql server 2008 stored procedure.
What is the best and easiest way to do this?
UPDATE: To make it more clear, I just want to call an aspx page from a stored procedure to notify my aspx page that data has been modified and aspx needs to refresh the cache.
I know that I can do this via sql cache dependency but i want to use a push rather than a pull kind of notification.
You could write a CLR function that accepts a host/ip address string and returns the number of seconds it took to ping, or -1 if it was unable to ping.
If you have xp_cmdshell enabled then you can use:
EXEC sys.xp_cmdshell 'PING 127.0.0.1'
Make sure that you understand the security implications of enabling xp_cmdshell though. It's off by default for a reason.
SQL Cache dependency does use a push notification via SQL Service Broker. You just need to set up your application to subscribe to notifications. That would definitely be my first choice.
If for some reason that isn't suitable maybe this approach using Object Automation would work for you? Edit Actually I was just reading the comments under the article and the original author suggests not using this in SQL2005 but using CLR instead if it is necessary to request a URL.