Database Synchronization 1

[DZoolander]

Hello,

I am in the technical specifications phase of a project that will involve extracting data from MAS 90 into a SQL satabase for a forecasting system (no writing of data back to MAS 90 will be involved).

I am trying to research a good way to synchronize data in our SQL database with MAS 90. I don't want to have to replicate the entire database each time. The SQL piece will sit on a web server, but I am open to having an intermediate database sit on the file server or a workstation.

Has anyone done anything like this? Any help would be much appreciated, even if it is strategic in nature. I will post about the full solution once it has been implemented.

Thanks!
Michael

 

[jamor1999]

I have only done this using SQL DTS Packages and that required importing the data quite often from the MAS 90 server. If anyone else has another approach, I'm also interested in learning it.

 

[DZoolander]

Thanks for the response - I don't mind importing data often as long as it is not ALL the data. Did you have a method for extracting only that data which has been added or updated?

Thanks much,
MM

 

[jamor1999]

Now that's an interesting idea. That would sure would help. I can't think of a way to do this... hmm. How would you accomplish this in any other database? Say we wanted to determine new records in a SQL Database in order to extract them to another database. How could be accomplish that??

Maybe that will get us thinking... any ideas?

 

[DZoolander]

In SQL server I would set up a default time stamp when I designed the databse, but I'm not sure how to otherwise. I don't see such fields in the MAS 90 tables.

Also, is there any kind of data dictionary or mapping of relationships in the MAS 90 tables? When I look at the tables in Access, there are no relationships and I'm starting to get a little confused by the data. My head is exploding.

 

[DZoolander]

In SQL server I would set up a default time stamp when I designed the databse, but I'm not sure how to otherwise. I don't see such fields in the MAS 90 tables.

Also, is there any kind of data dictionary or mapping of relationships in the MAS 90 tables? When I look at the tables in Access, there are no relationships and I'm starting to get a little confused by the data. My head is exploding.

 

[jamor1999]

It's normal to get confused by the data. I still get seriously frustrated...

The timestamp is definately not an option for MAS 90. Some records have a posting date, but that's not even half of them. Even if there was a timestamp field (did you mean for each record in the each table?), I don't think MAS 90 would respond kindly to a query with a clause like WHERE TimeStamp > 01/01/2005

I think it would be too large a query for it to handle gracefully, especially if there are other users in the system. That's only theory though...

The TRSG can certainly help you track down the fields your looking for. I usually open the PDF for the module I'm working on and perform a search for the field name (e.g. "invoice" or "invoice date").
As for a "relationship map," I am yet to find one.

When I was really in over my head and feeling overwhelmed, here was my plan of attack:

1) Import all the tables into my SQL "junk" databasefor the module or modules I was working on. (Don't forget, other modules may have the data you're looking for.)

2) SELECT * FROM table1 and analyze the results. Look through the columns for data that resembles what I'm looking for.

3) SELECT * FROM table2, then table3 and so forth....


I still do this when I'm writing reports or corrected data descrepancies and can't find the one field I need. It's hard work, but it works and in the end you become very familiar with the table structures.

And the best part, you can manipulate the data and only import the columns you need and create your own tables in SQL to suit your needs.

You could always post your data and table relationship questions here, of course ;-)