Can i use SQLCLR to call a webservice - and if so can this be done without waiting for its repsonse

I want to use the SQLCLR to call a webservice via triggers e.g. Action on one database will casue another action on a third party system.

Is this possible, if so how, and any code samples would be greatly apprecated

Who is Participating?
NightmanConnect With a Mentor CTOCommented:
>>the problem is performance
In process (if possible) will kill your SQL Server if not called asynchronously, which I don't believe is possible.

1. I agree with you that you should not use cmdshell
2. Why would this drain performance? This will be running single-threaded (default) or you could have a thread pool if you like.

Using service broker (for queueing and notifications) you can have the windows service running on a different server if you like. Because this will not be a drain on your primary SQL Server (and happens out of process) it will be more efficient. Also, if multiple records are inserted at the same time, you won't have SQL Server trying to spawn multiple concurrent, synchronous threads to call out to an external web service. They will simply be inserted into the queue, which should be *very* quick.

You can call out to Service Broker from your windows service (have a look at examples in SQL books online for WAITFOR and RECEIVE) - this should act as a notification to your sevice (push model, rather than polling your database), which can instantly (or near enough) call your web service. Service broker also gives you that persistence layer, so you don't *lose* messages from your queue. If I remember correctly Eric Nelson published a "Hello World" example of this on his blog, but I can't get to the page right now. Just Google "Service Broker WAITFOR RECEIVE samples .NET".

While this may be more work in the long run (but not a LOT more work), it is definitely a superior (and more reliable) design.
I guess it would be possible to call out (I have never tried it), but I sincerely doubt that you would be able to do it asynchronously. And even if you can do it from CLR, I suspect that this may not be the best design (and certainly not synchronously!).

Perhaps you need to assess your design - trigger drops an entry in another table, and you have another process (external service) handling the out of process transactions seperately (possibly use Service Broker to queue these).
paulCardiffAuthor Commented:
I agree with you 100% but the problem is performance i.e. The reason i want to do it this is is because i need to be alterted at real time - when the db has been updated - and once alterted have some .net code run.

Now if i change teh design this needs mean that either i use the cmdshell to run and exe or alternativly i have a windows service repeatidly inspecting the db. Now my problems are as follows
1) cmdshell is apparently quite dangerious in that it poses a security risk
2) using we windows serverice to run repeatidly will have a drain in performance.

Thus the reason i was looking to use a windows service.

With all this in mind what would you suggest?
paulCardiffAuthor Commented:
sorry meant to say * "Thus the reason i was looking to use a web service call"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.