FIX: "Conversion failed" error when the UNION ALL clause runs against a BAMPrimaryImport view in BizTalk Server 2010

Symptoms

Consider the following scenario:

• You have a computer that is running BizTalk Server 2010.

• You use Business Activity Monitoring (BAM) Management utility (BM.exe) to deploy a Business Activity Monitoring (BAM) activity definition file to a database.

• You use BM.exe to perform a BAM database update that adds some columns of various data types into an existing BAM activity.

• You run a SELECT statement on a view of the BAM activity. The view is created by using a UNION ALL clause that merges two views in the BAMPrimaryImport database.

In this scenario, you may receive a "Conversion failed" error message.

For example, a statement that uses the UNION ALL clause creates the [bam_<Activity Name>_AllInstances] view by merging the [bam_<Activity Name>_ActiveInstances] and [bam_<Activity Name>_CompletedInstances] views. Assume that the view has some records and that you run the SELECT * FROM [BAMPrimaryImport].[dbo].[bam_<Activity Name>_AllInstances] statement. In this scenario, you may receive the following error message:

Cause

This issue occurs because the order of the columns is not consistent.

For example, a NVARCHAR column from the first view is positioned where a DATETIME column is positioned in the second view. Therefore, the error that is mentioned in the "Symptoms" section occurs.

Resolution

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

For information about the service packs and cumulative update list for BizTalk Server, click the following article number to view the article in the Microsoft Knowledge Base:

2555976 Service pack and cumulative update list for BizTalk Server
For more information about BizTalk Server hotfixes, click the following article number to view the article in the Microsoft Knowledge Base:

2003907 Information about BizTalk Server hotfixes