0

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

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/

Community
  • 1
  • 1
opensas
  • 60,462
  • 79
  • 252
  • 386
  • I would create an application service that is responsible for performing this work, rather than doing it directly from SQL Server. That way you don't have to worry about the SQL Server version (so much)... – Mitch Wheat Jul 22 '09 at 02:38
  • yes, I understand what you mean, the problem is that we're talking about an application that has almost all of it's business logic coded in stored procedures, and we'd like to keep it that way, cause the stored procedures are accessed directly from several places... I mean those stored procedures are some kind of api to the aplication... – opensas Jul 22 '09 at 02:44

1 Answers1

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...

opensas
  • 60,462
  • 79
  • 252
  • 386