GeekZilla

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

Please beware:

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.

Author Dave Howard

I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.

Comments

Add Comment

Latest Articles