Post reply

Copying db/table info for Reporting

  • June 29, 2007 at 5:26 am

    #177221

    Hi everyone,

    I have a small issue with a project I'm working on - it's a MS Great Plains site which I am writing a small budgeting app and some reports for.

    Unfortunately the setup of the site was not clear initially and now I have come across the problem that the databases I wish to report on reside on 3 different instances on the same server.

    Basically there are 6 databases which I need information from - 2 on each instance.

    3 of the databases will all be named identically with the same tables but different data. (i.e. 3 databases called DYNAMICS - one on each server)

    The other 3 databases will be named according to the company they belong to and there will be one for each instance. So instance 1 may contain database 'COMPANY1' and instance 2 will contain 'COMPANY2' etc.

    Is there a way to consolidate all of this information into one location (i.e. another instance?) so that I end up with a server that contains...

    one DYNAMICS table that contains all the rows from the 3 dynamics tables on the 3 instances (not worried about the primary keys at all, they can be changed)

    three databases 'COMPANY1', 'COMPANY2', 'COMPANY3' which contain the data and structure from the corresponding databases on the 3 instances

    Any help would be much appreciated!

    Charles

  • June 29, 2007 at 6:00 am

    #716131

    1. create linked servers on a chosen instance which will serve as the main reporting point. and then either 2 or 3 below

    2. you can then use dynamic scripts to get data from all the databases and feed into your report

    3. if the data does not change much in a time period you can consider creating temp tables which will be fed data from the other databases. and then run your reporting off that.

    Everything you can imagine is real.

  • June 30, 2007 at 9:54 am

    #716370

    Thanks, I asked round and someone else pointed me at linked servers. At least that way the reports can be real time and I don't have to worry about dts packages failing or stuff like that.

    I already use a lot of dynamic SQL to generate pivot style tables so it should be easy enough to feed in the instance names to the select statements.

    Cheers for the help!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply