invalid object name bam_resendTrackingActivity_AllInstances (Microsoft SQL Server, Error: 208)

Question

BT2013 - I get the error in the above Title when I'm in group hub and click on "AS2 Message and Correlated MDN Status". 
We have had one AS2 customer working for a year, and about to add several new ones.  
BAM is configured, but not being used.  BizTalk Configure utility also shows the checkbox is checked for "Enable BizTalk EDI/AS2 Runtime Status Reporting for this BizTalk Group". 

Please advise,

Neal Walters 

P.S.  Got this additional info when clicking on the icon at the bottom.  I'll see what that SPROC does... 

Error Number: 208

Severity: 16
State: 1
Procedure: bts_GetAS2StatusRecords
Line Number: 18

It's in the BAMPrimaryImport Database, and it does a join to two tables that don't exist: 

LEFT OUTER JOIN bam_ResendTrackingActivity_AllInstances AS resendTable ON
resendTable.BTSInterchangeID = mesgTable.BTSInterchangeID
LEFT OUTER JOIN bam_ResendJournalActivity_AllInstances AS journalTable ON
journalTable.TrackingActivityID = resendTable.ActivityID
AND (journalTable.ResendStatus = 'ResendAttemptsExhausted'
OR journalTable.ResendStatus = 'ResendTimeout')

P.P.S.  I turned MDN tracking on for an existing vendor, and it prompted me: 
"Enabling content storage in non-repudiation database requires AS2 reporting to be activated.  Do you want to activate AS2 reporting?" and I clicked the "Yes" button.  But same error above. 

"

Answer 1

Bump - Any help here please? 

Thanks,
Neal 

Answer 2

Please any ideas? I'd like to see my BAM report. 

That table is on our test system, but not our production system. 
Would it be safe to add it manually? 

Neal 

Answer 3

You might have to raise a support ticket with Microsoft for this.

Answer 4

Does that view exist in the database?

Answer 5

View in on Test, but not on Prod. 
Here is the view, neither of the tables in it are on the Prod system, but are on the Test system. 

What is it that creates the tables and views to begin with? 

Neal 

                      

CREATE VIEW [dbo].[bam_TransactionSetActivity_AllInstances]
        AS

              SELECT * FROM [dbo].[bam_TransactionSetActivity_ActiveInstances]
              UNION ALL 
              SELECT * FROM [dbo].[bam_TransactionSetActivity_CompletedInstances]

GO

Answer 6

Deploying the BAM Tracking Profile creates all the DB stuffs.

So, you need the profile .xlsx and deploy that using the command line tools.  (It might export an xml, sorry, it's been a while...)

Answer 7

Johns-305 - So, you are saying there is a report that is built into BizTalk Hub that requires some BAM tracking profile to be created?  Just to be clear, the report from the hub I'm trying to run in highlighted below: 

I checked in the BizTalk install directory, and couldn't find any .xlsx template other than the ones in the SDK. 
Surely you are not suggesting I just deployed any template, then suddenly the above report starts working? 

I usually work more on the development side, but having to act as administrator as well on current contract. So I don't remember looking at the above report in the recent past. 

Meanwhile, I found a nice query here that I can run in the meantime.  

https://mubarikahmed.wordpress.com/2015/12/18/biztalk-as2-messages-with-pending-mdn/

Also, I posted this earlier: 

CREATE VIEW [dbo].[bam_TransactionSetActivity_AllInstances]
        AS

              SELECT * FROM [dbo].[bam_TransactionSetActivity_ActiveInstances]
              UNION ALL 
              SELECT * FROM [dbo].[bam_TransactionSetActivity_CompletedInstances]

GO

and found out that first select above is also a VIEW, and it's there, and it's underlying table is there. 

Thanks,

Neal 

Answer 8

Sorry, I should have scrolled up to remind myself of the object in question...

So, if you have EDI/AS2 Reporting enabled, that should be there.  Are all the other tables, InterchangeStatus, TransactionSetActivity, etc there?

If they are, then someone deleted the Resend table(s).  If not, you need to add EDI/AS2 Status Reporting in BT Config.