Calling a web service from SQL

  • Hi there,

    We have a sudden requirement for a solution outside our usual skillset. A third party company has provided us a web service that is going to look into our database to extract information before sending it off to a website for analysis.

    Basically (as backwards as it is - but we can no longer alter the behaviour of this third party product) we need to call the webservice whenever a certain record arrives in the db and it will then look at the whole table for things to pass over.

    Now I know you can call the web service using CLR created through .net. But every example I read is from people calling the web service to actually get some data back. Is there a way of just kicking off a web service from within SQL? Alternatively - if we have to go down the CLR route I can't get my head around which is the best option - a UDF or a Stored Procedure? Performance issues? Relevance?

    Thanks

    Shark :hehe:

  • I'm just jumping in here to watch this thread - I'm going to have to figure out how to do almost the same thing in the near future. So I'm just going to camp out here in the corner and see if someone comes along with a handy signpost to point the way...

    -Ki

    -Ki

  • Is there a better forum section to add this question under rather than general?

  • There are a number of possible solutions to this. Are you planning to do this as a batch process or as close to real-time as possible?

    If it can be a batch, either calling a CLR, or using SSIS with a script component destination are probably two of the least painful options (I'd go for the latter personally). Either would require some .Net knowledge, but are fairly simple to implement.

    If it needs to be very close to real-time, Service Broker may be a possible option (which allows asynchronous queue based communication over web services), although it may be overkill for your purposes and need quite a lot of co-operation from the vendor.

  • With my knowledge geared more towards Admin than Development - I can't decide whether a CLR UDF or Stored Proc suits it best?!

  • Well, I've never tried, but I assume that CLR UDF's have similar restrictions to T-SQL ones in that they're specifically not for this kind of purpose (e.g. data modification), so if you're going down the CLR route, a stored procedure is clearly the option to choose.

  • Does the web service run within the context of the CLR then or does it run it outside?

    I have found out that the web service that is to be called from SQL is just doing a lookup of available data, and then passes the key on to another web service that uses it to select the data and inserts it into the third party backend.

  • You have no control about what the web service does after you've made your call. I guess it's likely that you'll physically be able to push data out to a web service from a UDF as SQL Server won't be able to distinguish the difference.

    However, it would be bad practice, so I'd strongly suggest doing it as a CLR Stored Procedure (it is no more effort) if you're doing CLR at all.

    Do you really want to have SELECT statements that effect change (even if externally)?

  • Sorry no wasn't meaning to have any control over the Web Service, or to pass anything to it.

    The CLR literally just has to call the Web Service. It doesn't have to do any data modification within the UDF. So with that in mind would you still say Stored Proc over UDF? (my dev team seem very excited by, and are pushing me for the UDF option!)

    Thanks for your help so far!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply