Hi,
I want to know if it is possible to create and execute a http request in SQL Server. I want to run the request in the database as alot of the data in the url would be retrieved from the database.
I want to create a stored procedure that runs the request. The stored procedure must be called from a trigger on a table.
The reason I want to go this way is because it would be alot faster that to get the data from the database and then execute the http request in the application. Another reason that I want it done on the database side is because the internet connection of my client is not as fast as the one I am using for development so I don't want the pages to load to many times and get the data between the server and the client computer through as fast as possible.
Any ideas on how I would do this?
Without a more detailed understanding of your particular problem domain, it's difficult to be certain of this but in general I'd recommend against this approach. It seems like a poor use of resources that could be utilized by the core database services. For example, if the triggers are synchronous operations, I would imagine that they would hold a row lock for the duration of the HTTP request...and given that network requests are usually high-latency operations, that wouldn't be a good thing in a multi-user environment.
If the web data that is related to the row data is unchanging or slowly changing, you could consider using a service to collect this data and cache copies of it in the database (a benefit of which is that you would have a DB only query from your client application). If the web data changes frequently/continuously, expecting your DB client to handle this amalgamation seems appropriate. That client code could exist in the actual end-user client application or, if you're comfortable with multi-tier application programming, behind a web service that aggregates all of this information for the end-user client application (NOTE: your SQL Server can be exposed as a web service, which could help consolidate the design of the overall system).
You may also want to consider looking at SQL Server Integration Services...this technology is meant to aggregate data from heterogeneous sources as you've described.
As to writing a stored proc that makes the web request, I don't see anything in the TSQL reference that stands out as a likely candidate, but this meets my expectations for the reasons I mention above. If you were to write an extended stored procedure (see http://msdn2.microsoft.com/en-us/library/ms164716.aspx for more information), you could do whatever you wanted. You could also potentially use CLR Integration (additional information is provided via a link at the previous URL). Note that in both cases, you'd likely have to manage issues around configuration and delegation.
|||Thank you!
I think I'll go the web service route on this one then.
The data is slow changing. I want to do the following:
I'm writing a system that manages rugby referees. A member would be appointed to official a game as either the referee or a touch judge and then there would be performance reveiwers appointed on some of the game. The web request functionality come in when the member would be notified of his appoint on a specific game and would also be notified if there are any changes on that specific game he appointed on - this would then happen for all the game he/she is appointed on.
You've mentioned the of a web service...from what I understand from my problem and what you have said, a web service would be a good solution? From what I understand what would happen with the web service is that I would monitor the specific table and then act as soon as there are changes to the table?
I am not at all familiar with SQL Server Integration Services or CLR Integration so I wouldn't actually know which would be the better more effective solution. If you need me to give more detail on what should happen I can do so, but I need the best and most effective solution. I have more than enough resource about SQL Server so if I don't know how to do it I would be able to find out, but I couldn't find anything on HTTP requests in SQL so I wanted to know if it was possible seeing I couldn't get any information.
|||What does the web request do? It seems like the intent of the system is to send a notification when some property of the match changes (e.g. start time changes from 4pm to 5pm)...I can envision a web front end for referees, touch judges, and performance reviewers to enter information (e.g. an email address to which schedule updates should be sent), but I'm not yet clear as to why this data would reside outside the database.|||It should send out an sms to the referee who's match it is so that he/she can be notified of the change.
Email would also be used. The email will be used when a member is appointed, then the details will be emailed to the individual.
|||Aaaah! I get it now...I was thinking the HTTP request would be pulling information into the database, not pushing data out via an HTTP POST or something similar.
Since many carriers allow for email to be routed to their customers phones via SMS, can you have a general email delivery of information? As an example, Cingular allows you to send an SMS to someone from your email account by sending the message to <10-digit phone number>@.cingularme.com. This would allow you to use sp_send_dbmail to send all of your outbound info...you could then choose different reports/queries to generate different notification types.
If that doesn't work for you, your next best bet is to write either an extended stored procedure or a managed stored procedure.
|||Ok help me here.
I will be hosting remotely on a webserver over which I do not have control. The service provider won't allow me to send email throuh the server and from what I can gather I do not have access to SQl server integration services on that server.
With regards to the Extended Stored Procedures I understand this from it - help me if I'm wrong. I have to compile a dll which would need to be executed by a stored procedure. The dll would need to be on the same server as the database? When the stored procedure is then executed, it will pass the results to the dll and methods in the dll will then be executed?
So if I understand correctly it will work as follow in my sittuation...
A referee is appointed on a specific game or details on a game he is appointed on changes. When this happens I call a stored procedure which in turn call a dll that I have written which sends the notification to the referee.
You said something about a webservice as well. How would I be going about to send the data to the webservice to perfrom the functions a I require?
I have gone through a couple of stuff of which this one is best from what I have seen. I am still to figure out precily how I would impliment that to solve my problem but I will get there. In the meantime some extra help will do!!
Thanks
|||That gives me a much better idea of the limitations you're faced with...
Your understanding of the extended stored procedure is essentially correct (NOTE: you could also potentially use a managed stored procedure -- which is the CLR integration you've linked above -- that used web request functionality from the .Net Framework...not sure if the security policy within SQL Server would support that, however). When a table update happened, a trigger would call the extended stored procedure.
Your options, as you've discovered, are somewhat limited because you don't control the server setup. Perhaps it would be drastically easier to implement a polling solution...as a simplistic example, you could implement a service/app that ran on your machine and periodically (every hour?) queried the database to determine if there were updated records for which updates needed to be sent.
No comments:
Post a Comment