My misunderstanding about how BizTalk builds MSI packages for applications

A misunderstanding about how BizTalk builds MSI packages for applications caused me some recent heartache.

Let’s say I have a BizTalk “application” with the following resources:



As you can see, I’ve got a BizTalk assembly, a “standard” .NET assembly, binding file, text file, and virtual directory (for a web service). In my simple mind, I see the “Source Location” column, and assume that when I walk through the wizard to build an MSI package, BizTalk goes and grabs the file from that location and jams it into the final package.



I was quite wrong. I learned this when I made updates to my web service and helper components, rebuilt my MSI package, deployed it, and learned to my horror that “old” components had been installed on the destination server. What gives?

Whenever you add a “resource” to a BizTalk application (either by deploying your BizTalk library, or manually adding an artifact), it is added to a CAB file and stored in the database. In the BizTalkMgmtDb database there is an amazingly well-hidden table named adpl_sat which holds these uploaded resources.



You can see in that picture that properties for each artifact are stored (e.g. GAC-ing components on install), and there’s also a column called “cabContent” which stores binary data. Updating artifacts on your file system does NOT mean they will get included in your MSI packages.

So what’s a guy to do? Well, you may have noticed that for most resources, when you right-click and choose “Modify”, there is a button where you can “Refresh”. Now, instead of just refreshing the file from the spot designated in the “Source Location”, it asks you to browse to where the updated file resides. That seems a bit unnecessary, but whatever.


So, that’s how you refresh MOST resources and make sure that your BizTalk application stays in sync with your developed artifacts. A big gotcha is, you CANNOT do this for virtual directories. I can’t seem to identify any way to refresh a modified web service short of removing the resource, and adding it back in. Big pain. Given how crucial web services are to many BizTalk applications, I would think that both adding them as resources (via UI vs. command line), and easily refreshing them, should be a priority for future releases.

Producing XML from a multi-table join in SQL Server

Given a database schema with a parent table and two or more child tables.
--------------------------------------------------------------------------
Is it possible to create a query, using the for xml statement, that outputs the XML:
---------------------------------------------------------------------------
My initial attempt:
---------------------------------------------------------------------------
select person.name, person.age,
address.streetAddress, address.town, address.postcode,
contact.type, contact.value
from Person as person
left join Address as address on person.PersonID = address.PersonID
left join Contact as contact on person.PersonID = contact.PersonID
where person.PersonID = 1
for xml auto, elements

Yielded result in which all the combinations of contact and address are output:

Using a single left join to either the Contact or Address table produces part of what I'm after, but after adding the second join it starts to go wrong. FYI...it works a treat! I believe the "XML PATH" help me by writing below query
--------------------------------------------------------------------------
SELECT person.name, person.age,
(
SELECT address.streetAddress, address.town, address.postcode
FROM Address as address
WHERE person.PersonID = address.PersonID
FOR XML PATH('ADDRESS'), TYPE
),
(
SELECT contact.type, contact.value
FROM Contact as contact
WHERE person.PersonID = contact.PersonID
FOR XML PATH('CONTACT'), TYPE
),
FROM Person as person
WHERE person.PersonID = 1
FOR XML AUTO, ELEMENTS
---------------------------------------------------------------------------

IIS Fails with error “App-Domain could not be created”

Getting the error “Failed to execute request because the App-Domain could not be created.” can be fixed by doing the following to “re” register ASP with IIS.

Other descriptions of this error can be

Error: 0×80131902
Failed to initialize the AppDomain:/LM/W3SVC/1/ROOT
Exception: System.Configuration.ConfigurationErrorsException

SOLUTION:
--> adding the IIS_WPG read/write access also worked for me, but either make sure that all the subfolders inheret their parent dir permisions settings or apply the permissions to EVERYTHING inetpub and below. I went to IIS and right clicked on my website, clicked permissions, then added IIS_WPG from the local server (we also have a IIS_WPG domain group). I only left the read box checked and it solved the problem. still if not then follow below steps

-->First Stop Web Services by typing the following at the command prompt:
net stop w3svc

Uninstall all instances of ASP.NET by running the following command:
aspnet_regiis.exe -ua

You are now ready to re-install ASP.NET to IIS with the following:

aspnet_regiis.exe -i

Lastly you need to start up the webserver:

net start w3svc