We started getting the following BAM warnings and errors in the event viewer every time our BizTalk application received a transaction.
Event Type: Warning
Event Source: BAM EventBus Service
Description:
Execute batch error. Exception information: TDDS failed to batch execution of streams. SQLServer: ServerName, Database: BAMPrimaryImport.Parameter is not valid.
Event Type: Error
Event Source: BAM EventBus Service
Description:
Execute event error. Error(s) occurred while executing events, see TDDS_FailedTrackingData table for more details. SQLServer: ServerName, Database: BAMPrimaryImport.
The BAMPrimaryImport.dbo.Bam_Metadata_TrackingProfiles table logged the following errors:
- TDDS failed to execute event. Could not find stored procedure ‘dbo.bam_PartsInvoice_PrimaryImport’.
- TDDS failed to execute event. Could not find stored procedure ‘dbo.bam_PartsInvoice_EnableContinuation’.
- TDDS failed to execute event. Could not find stored procedure ‘dbo.bam_PartsInvoice_AddRelationship’. Could not find stored procedure ‘dbo.bam_PartsInvoice_PrimaryImport’.
- TDDS failed to execute event. Parameter is not valid.
- TDDS failed to execute event. Parameter is not valid.
- TDDS failed to execute event. Could not find stored procedure ‘dbo.bam_PartsInvoice_PrimaryImport’.
Solution 1: (Supported by Microsoft – Didn’t work for us)
It appears that we have orphaned tracking profiles that are still trying to log information for an old activity that was deleted. I tried the suggested fix from Microsoft on How to Remove Orphaned Tracking Profiles which entails the following steps:
- Re-deploy the activity using the command line (bm.exe deploy-all -DefinitionFile:OldDefinitionForActivity.xlsx).
- Open the Tracking Profile Editor go to File -> ImportBAM Activity Definition.
- Locate the BAM activity definition name that matches the name associated with the errors in the Bam_metadata_TrackingProfiles table.
- Check the Retrieve the current tracking settings for this activity definition check box.
- Once the activity loads go to Tools -> Remove Tracking Profile
- Delete the activity using the command line (bm.exe remove-all -DefinitionFile:OldDefinitionForActivity.xlsx).
This didn’t fix our problems, we continued to get same TDDS failed to execute event errors.
Solution 2: (NOT Supported by Microsoft – Didn’t try this one)
I found a fix in the Pro Business Activity Monitoring in BizTalk 2009 book by Apress that comes with a warning that it can void Microsoft support for your BizTalk environment. Microsoft has a blanket statement that if you manipulate the BizTalk databases they are not obligated to support them. The book suggest truncating all data from the following tables:
- BizTalkMgmtDB.dbo.BAM_TrackingProfiles
- BizTalkMgmtDB.dbo.BAM_TrackPoints
- BizTalkMgmtDB.dbo.StaticTrackingInfo
- BAMPrimaryImport.dbo.BAM_Metadata_TrackingProfiles
- BAMPrimaryImport.dbo.BAM_Metadata_Annotations
I’m not a big fan of this approach since it would require you to redeploy all tracking profiles and also go into the BizTalk Admin Console and enable/disable tracking on all ports, pipelines, and orchestrations. Blowing away all of the data in these tables seems a little excessive and I believe it will cause more issues, so we didn’t try it.
*Solution 3 (NOT Supported by Microsoft – Works!!!)
I didn’t want to do it this way, but with the limited time and resources available to figure it out I ended up doing the following:
- To be safe make a backup copy of the BAMPrimaryImport database before continuing.
- Re-deploy the activity using the command line (bm.exe deploy-all -DefinitionFile:OldDefinitionForActivity.xlsx). This generates the stored procedures in the BAMPrimaryImport database.
- Open SQL Profiler and start a trace with a filter for DatabaseName
like
BAMPrimaryImport. Do a search on all columns for the activity name, in my case it’s “PartsInvoice“. This will allow you to see the parameter names and data that is used to call the stored procedures. - Modify stored procedures in the BAMPrimaryImport database to use the same parameter names and data types that you see in SQL Profiler. Then replace everything between the BEGIN and END tag with a RETURN statement. This will allow the orphaned tracking profile to call the stored procedure, not throw a warning or exception, and it won’t log any of the unwanted data.
Here are the updated stored procedures that I used:
ALTER PROCEDURE [dbo].[bam_PartsInvoice_AddRelationship]
(
@ActivityID NVARCHAR(128),
@ReferenceType NVARCHAR(128),
@ReferenceName NVARCHAR(128),
@ReferenceData NVARCHAR(1024),
@LongReferenceData NTEXT
)
AS
BEGIN
RETURN 1;
END
_____________________________________
ALTER PROCEDURE [dbo].[bam_PartsInvoice_EnableContinuation]
(
@ParentActivityID NVARCHAR(128),
@ContinuationToken NVARCHAR(128)
)
AS
BEGIN
RETURN 1;
END
_____________________________________
ALTER PROCEDURE [dbo].[bam_PartsInvoice_PrimaryImport]
(
@ActivityID NVARCHAR(128)=NULL,
@IsStartNew BIT,
@IsComplete BIT,
@PassedValidation DATETIME = NULL,
@FailedValidation DATETIME = NULL,
@Vendor NVARCHAR (50) = TEST,
@PO$Number NVARCHAR (50) = TEST,
@Invoice$Number NVARCHAR(128)=NULL,
@InvoiceReceived DATETIME = NULL,
@InvoiceProcessed DATETIME = NULL,
@InvoiceAmount FLOAT = 1.00,
@TotalInvoiceAmount FLOAT = 1.00
)
AS
BEGIN
RETURN;
END
Michael Woods Blog 