none
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. 

    "

    • Edited by Neal Walters Friday, May 24, 2019 6:32 PM More details
    Friday, May 24, 2019 4:25 PM

All replies

  • Bump - Any help here please? 

    Thanks,
    Neal 

    Thursday, May 30, 2019 4:34 PM
  • 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 

    Thursday, June 6, 2019 2:55 PM
  • You might have to raise a support ticket with Microsoft for this.
    Friday, June 7, 2019 1:15 AM
  • Does that view exist in the database?
    Friday, June 7, 2019 12:32 PM
  • 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

    Wednesday, June 12, 2019 3:25 PM
  • 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...)

    Wednesday, June 12, 2019 7:01 PM
  • 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 


    Wednesday, June 12, 2019 9:36 PM
  • 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.


    Wednesday, June 12, 2019 9:49 PM