XMLHTTPRequest in Stored Procedure
I recently had a requirement to call a webservice from a stored procedure. I looked into doing this by writing a CLR assembly in SQL 2005.
There were a few articles online describing this but working in a large organisation I knew that getting the thing working on my development was only 20% of the battle.
The XML serialisation of the web service request/response cannot be done natively, a separate assembly needs to written to achieve this, using a tool such as SGEN.exe. Basic objects such as HTTPRequest are not available in the SQL CLR so you can't just avoid the serialisation.
Even once I had all that working, the idea of our database team deploying assemblies to their databases just seemed like a dream.
So I looked for a simpler method and found that I could create an XMLHTTPRequest within a stored procedure much like I would in client-side code for an ajax solution.
The stored procedure is below...
CREATE PROCEDURE HTTP_REQUEST ( @URI varchar(200), @response varchar(8000) OUT ) AS DECLARE @xhr INT ,@result INT ,@httpStatus INT ,@msg VARCHAR(255) EXEC @result = sp_OACreate 'MSXML2.XMLHttp.5.0', @xhr OUT IF @result <> 0 BEGIN RAISERROR('sp_OACreate on MSXML2.XMLHttp.5.0 failed', 16,1) RETURN END EXEC @result = sp_OAMethod @xhr, 'open', NULL, 'GET', @URI, false IF @result <>0 BEGIN RAISERROR('sp_OAMethod Open failed', 16,1) RETURN END EXEC @result = sp_OAMethod @xhr, SEND, NULL, '' IF @result <>0 BEGIN RAISERROR('sp_OAMethod SEND failed', 16,1) RETURN END EXEC @result = sp_OAGetProperty @xhr, 'status', @httpStatus OUT print 'Status: ' +convert(varchar(10),@httpStatus) IF @result <>0 BEGIN RAISERROR('sp_OAMethod read status failed', 16,1) RETURN END IF @httpStatus <> 200 BEGIN RAISERROR('sp_OAMethod http status bad', 16,1) RETURN END EXEC @result = sp_OAGetProperty @xhr, 'responseText', @response OUT IF @result <>0 BEGIN RAISERROR('sp_OAMethod read response failed', 16,1) RETURN END EXEC @result = sp_OADestroy @xhr RETURN GO
You also need to enable Ole Automation for this database using...
sp_configure 'Ole Automation Procedures', '1' GO reconfigure GO
So basically your T_SQL can now make http requests, but not web service requests.
The page I am requesting will be written in C# and will make the web service request itself, in an environment that is not so restricted as the SQL CLR.
The added benefit is that this does not require 2005 to work, it should work on any SQL Server database v7 onwards
Your application login will need access to run the sp_OAxxx extended procedures, so you will need to ensure that this isn't something that just anyone can login as. I have read that teh user needs to in the sysadmin role - I have found that just GRANTing EXECUTE permissions for your user on each xp is enough.
Also, do not make http calls from within a trigger, this can potentially cause really bad performance, especially where the web server is not reachable or responding for whatever reason.
I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.