Generating a Schema for the BizTalk SQL Adapter

To call a Stored Procedure from BizTalk you need to use the SQL Adapter. This requires you add a special Schema to your BizTalk application, this Schema is generated by a wizard in Visual Studio (the wizard is installed as part of the BizTalk SDK).

This process isn't as straight forward as it should be an its easy to end up with a Schema where the value returned from the Stored Procedure is represented by a "Success" Element of "anyType which is useless.

Firstly, you must have a Stored Procedure that that returns a result set using "FOR XML AUTO". Secondly, when you run through the wizard you must add "XMLDATA" to the end of the Stored Procedure (and remove it after you have generated the Schema). The XMLDATA clause causes SQL Server to return the Schema for the result set (as well as the result). So, when you go through the wizard you must have a stored procedure looking something like this:

SELECT ColumnA, ColumnB FROM MyTable FOR XML AUTO, XMLDATA

After you have generated your schema, remove the "XMLDATA" clause to leave:

SELECT ColumnA, ColumnB FROM MyTable FOR XML AUTO

When you run through the wizard, most of the values you need to enter are obvious but when you get to the "Schema Information" screen, you need to change the "Port Type" to "Send".

When you get to the "Statement Information" screen, you need to enter valid values into the "Value" column (click the Value cell twice to enter data). Then hit "Generate", then hit "Next".

The SQL Adapter Wizard does not seem to support "FOR XML EXPLICIT", you get an error that says "the required attribute 'name' is missing". However, I would guess that if you can generate the boiler plate Schema using "FOR XML AUTO," you should be able to change your Stored Procedure and then manually edit the Schema generated by the Wizard to match what your XML EXPLICIT format.

Microsoft SharedView Version 1.0 available

Microsoft’s desktop sharing application, called SharedView, is out of beta. Think of the application as “Live Meeting”-lite, where up to 15 people can all share one desktop.

Download from here: http://www.connect.microsoft.com/content/content.aspx?ContentID=5306&SiteID=94

Gated Check-ins and Team Foundation Server

There was a discussion lately on the ALT.NET mailing list about the merits of NAnt versus MSBuild. This discussion expanded to encompass the various build engines there are outt here such as CruiseControl.NET (open source from ThoughtWorks) and TeamCity (free for non-Enterprise use, by JetBrains).

Someone mentioned the "private build" feature of TeamCity where by you ask the build server to do a build of what you currently have but without actually checking in your code to the source tree.

I pointed out that this feautre is also supported by Team Foundation Server through an open source framework exisits called Gauntlet (http://www.opengauntlet.org/). The elevator pitch from the Gauntlet web site is as follows:

"OpenGauntlet...mimics a fundamental feature of Microsoft's internal development system (codenamed Gauntlet), also known as "Gated Checkins". It avoids the problems associated with large development teams whereby a single developer will check in non-compiling code, which subsequently causes delays in the development life cycle as the team waits for the issues to be resolved. It can also be configured to disallow check ins if the proposed code changes break the unit tests. The central principle is that users are not allowed to check code directly into source control. Instead, they shelve their changes with a specific naming prefix when they are ready to check some changes in, and OpenGauntlet unshelves their changes into a temporary workspace, checks the changes compile and pass unit tests, and then checks the changes into source control under the requesting developer's username."

I've not taken the plunge with setting this up yet, I have an upgrade to TFS2008 to do first, but it looks like a must have. Here's to less "red" builds!