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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.