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.


Joe said...

Update: Couldn't get this to work so we revisited the SQL Adapter and now are using that.

Eccleston said...

Did you get anywhere with this or are you using the SQL Adapter? Any hints for how to solve the issue of needing a port for every stored procedure? (Too many connection string to maintain!)

The SQL 2005 Soap seemed a perfect solution, but I had same issue as you with the WSDL.