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

Article translations Article translations
Article ID: 2610824 - View products that this article applies to.
Expand all | Collapse all

On This Page

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

Properties

Article ID: 2610824 - Last Review: December 1, 2011 - Revision: 1.0
APPLIES TO
  • Microsoft BizTalk Server Branch 2010
  • Microsoft BizTalk Server Developer 2010
  • Microsoft BizTalk Server Enterprise 2010
  • Microsoft BizTalk Server Standard 2010
Keywords: 
kbbtsbam kbqfe kbfix kbexpertiseadvanced kbsurveynew KB2610824

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com