Hi David. "Why use a Web Service?" is a great question. It could be to get a batch of data to work on from a service outside of the company, and the advantage here is that it can be done inline in the import process without needing to call an external app or have an external app scheduled, and then needing to import that data. So it can save a few steps. It can also be used to push data out of the system, possibly logging events outside of a Transaction so they can't get rolled-back. It can also be used to kick-off an asynchronous service.

The key to using a Web Service within SQL Server productively is to not use it for any silly reason, such as the understandably frustrating situation that you ran into. The problem there wasn't Web Services or even SQLCLR, but of silly people making baseless decrees about technology that they don't fully understand, rather than letting the team determine what is best.

And this can happen with any feature or technology. I have been directed to partition tables, no matter how small, because Table Partitioning was being used as a means of managing / helping a multi-tenant system in which each customer had their own Partition_Number. Not only did it not help, it actually hurt the system. We also had a rule that if you needed a "cursor" operation, you had to use a WHILE loop instead of a CURSOR since CURSORs are bad. Problem is, the WHILE loops are actually worse. And I have seen folks use JSON instead of XML because "XML is bloated". And this was on SQL Server 2012, with no built-in support for JSON, and storing that JSON in an NVARCHAR(MAX) field could easily take up more space than the optimized SQL XML datatype.