locked
BamPrimaryImport query RRS feed

  • Question

  • Hi there, 

    In BAM I can see the information I need, but this is per Invoice instance. 

    Lets say I have an Invoice, and is being sent over to an Archiving System of our client (which I have no access to).

    I was recently asked if I could provide a report of all Invoices that have been sent over to our client archive system. 

    The problem is that there may be iterally thousands of these invoices, and going through BAM step by step is not an option. 

    I was wondering; how can I query the BamPrimaryImport or BamArchive to get the information but include all the Invoices in a given time range. 

    How is BamPrimaryImport Organised, why am I seeing tables with some dates in their names but not every day etc. 

    I guess it's going to be a Join across many tables, but I would appreciate any imput I can get. 

    Thursday, December 8, 2016 11:51 AM

Answers

  • Hi

    In the BAMPrimaryImport database, you can SELECT data from all the tables using a SQL View - bam_<YourActivityName>Activity_AllInstances View. The result from querying this will be the UNION of all the data in the BAMPrimaryImport database for that activity.

    Now, if some of these activity tables have been archived in the BAMArchive database, you will have to perform an UNION of the above View and the individual archived tables for this Activity present in the BAMArchive database.

    You will get a better idea of how the tables are organized in these databases from the below blog-

    https://blogs.msdn.microsoft.com/biztalknotes/2014/04/01/understanding-bam-partitioning/


    Thanks Arindam


    Thursday, December 8, 2016 1:56 PM
    Moderator

All replies

  • Hi,

    The easy solution here is to give your business users access to the applicable BAM view, and tell/train them to get the information they need themselves. That is the purpose of BAM.


    Best regards, Kjetil :) Please remember to click "Mark as Answer" on the post that helps you. This can be beneficial to other community members reading the thread.

    My blog

    Thursday, December 8, 2016 1:29 PM
  • Hi

    In the BAMPrimaryImport database, you can SELECT data from all the tables using a SQL View - bam_<YourActivityName>Activity_AllInstances View. The result from querying this will be the UNION of all the data in the BAMPrimaryImport database for that activity.

    Now, if some of these activity tables have been archived in the BAMArchive database, you will have to perform an UNION of the above View and the individual archived tables for this Activity present in the BAMArchive database.

    You will get a better idea of how the tables are organized in these databases from the below blog-

    https://blogs.msdn.microsoft.com/biztalknotes/2014/04/01/understanding-bam-partitioning/


    Thanks Arindam


    Thursday, December 8, 2016 1:56 PM
    Moderator
  • There is no such table in my BAMPrimaryImport

    Thursday, December 8, 2016 3:20 PM
  • Hi

    It is a View, not a table as mentioned in my post above-

    "bam_<YourActivityName>Activity_AllInstances" View


    Thanks Arindam


    Friday, December 9, 2016 2:15 PM
    Moderator