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:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
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
Cumulative update information
The hotfix that resolves this issue is included in cumulative update package 3 for BizTalk Server 2010.
For more information about how to obtain the cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2617149 Cumulative update package 3 for BizTalk Server 2010
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
