Friday, August 11, 2006
Sql 2005 Web Services are driving me nuts
Today I have been trying to coax Sql 2005 to do something that I think is fairly reasonable. It seems to disagree. Sql 2005 has the ability to expose stored procs as web services. Cool. This means that I can consume the web service in BizTalk (for certain reasons I can't use the SQL adapter). So far so good. Let's look at the schema that BizTalk generates from the WSDL.
This is where things go terribly wrong for me. The schema tells me that sql returns one element, and digging into the WSDL tells me that it's of type SqlRowSet. This sucks since I can't exaclty fire up a new mapper in BizTalk and start creating records. What I wonder is how are people who are not from the .NET world supposed to make use of these Web Services? When I'm not doing BizTalk I write code in .NET so I'd be fine with this, but do all languages know what a SqlRowSet is?
I know there are ways to customize the WSDL that sql returns but this requires code. My client has a no in-house code policy so this is not an option. If I find a way to get sql to spit out a wsdl that is a bit more informative, I'll post an update. For now I'm stuck.