SQL Server 2k stored procs talking to webservices
This appears far easier to do in Skl2k5 (Yukon), but I've got SQL2K talking to webservices in production. I earlier blogged this strategy saying that I was backing away from using MSMQ in favor of WebServices in SQLServer.
There were a few difficulties because I wanted to send an XML document from SQL Server to the web service for processing. I would have to deal with (1) SQL Server limitations on string lengths and (2) proper encoding of the XML Document. My answer was to create a com object that allows for XML parameters to be added one at a time during runtime. While this limits the structure of the XMLDocument, the solution is flexible enough for the types of transactions being handled. After all the parameters are added, a method to perform the SOAP call packages up the XML, takes the passed URL and makes the call. Error handling is captured in the SQL Stored Proc and feedback provided according to the app structure. This turns out to be very clean.
The key component here is a C# class that exposes itself as a COM object. I then created an install to place it in the GAC and register the com object. From that point on, I configured the SOAP callpoint in the application and it's working like a charm. The solution works a bit like a poor-man's MSMQ - but when you have no control over the underlying network configuration (ActiveDirectory, workgroup, etc) this makes the installation details vastly simpler and builds on our existing integration engine.
Fun stuff...