how to consume a rest web service from SQL Server.

Question

I have the following scenario

• some real basic rest web service mainly, I'm just checking the existence of a single record, it's just a single validation, I'm not moving around hundreds of rows...

• that should be called from sql 2005, sql 2008... and yes sql 2000 and sql 6.5 (ouch!)

• I guess with sql 2005 and 2008 I could implement an assembly with HttpWebRequest...

• how about sql 2000 and 6.5??? maybe using curl thru xp_cmdshell ???

has anybody tried doing something like that?

-- some interesting links I've found

http://stackoverflow.com/questions/180046/sql-server-using-clr-integration-to-consume-a-web-service

http://blogs.msdn.com/sqllive/archive/2008/06/18/accessing-rest-based-web-services-using-sql-clr.aspx

http://www.simple-talk.com/sql/sql-server-2005/practical-sql-server-2005-clr-assemblies/

http://curl.haxx.se/

Answer 1

hey, what do you think of this solution that just came to my mind

I create a table in sql with the following fields:

id, url, request, response, http_status, domain_user, domain_password, result, begin_time, end_time

and I create a visual basic exe, or just a vbscript, to be run from the shell with the following info

call_rest.vbs server, db, table, id, dbuser, dbpassword

or just

call_rest.vbs udl_file, table, id

call_rest, connects to the database (using dbuser & dbpassword), reads the data from the table, calls the rest web service (impersonated as domain_user) and writes the result to response, http_status and result fields (it can also complete begin_time and end_time, for auditing purposes)...

I think it may work, and I could use that solution for all version of sql...