MAS9 to SQL

I am trying to transfer table structure from MAS90 to an SQL database, does anyone know how to do that?
heenadosaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tbsgadiCommented:
0
rr_milesCommented:
There is a MAS90 Master Developer product from DSD (kind of expensive) or you can try it by hand using the MAS90 TRSG and VBS scripting (inexpensive but not as fast). The DSD product will create SQL tables and perform updates to the SQL files when MAS90 data is updated. I have used both methods.
0
rr_milesCommented:
Your question asks about coping MAS90 file structures to SQL tables. What is your ultimate goal?

1) Transfer one MAS90 file to one SQL table.

2) Transfer several MAS90 files to several SQL tables.

3) Transfer several MAS90 files to one SQL table.

4) Transfer MAS90 data to SQL tables when MAS90 is updated. (Backup, Reporting Analysis, Integration with other systems)

5) Return SQL data to MAS90 when SQL is updated. (Online Order System)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

rr_milesCommented:
Here is my favorite tool to do what I think you want do. It has a TXT and a JPG, for the open folder. It will generate a SQL script from a MAS90 file.  It is open source so you can modify it using Notepad. Rename the TXT to HTA (VBScript with an interface)

Run it on a workstation that has MAS90 installed on it. Enter your MAS90 login info, click on the open folder icon to browse to select a MAS90 file. Enter your SQL database name. The SQL table will get its name from MAS90, but you can change it. Click Preview to view the script. Click Save to save the script. Modify the script as required.

mas2sql.zip
0
heenadosaAuthor Commented:
Folks, thank you.  I will be trying out these solutions this weekend and let you know what happens.
0
zephyr_hex (Megan)DeveloperCommented:
i did this without buying the $10K product offered by DSD.

it's a matter of creating tables in SQL, and then setting up a linked server to MAS.  then use SQL Agent to schedule data import.

http://sagemas.lithium.com/t5/Sage-MAS-90-and-200-Extended/ODBC-with-DSN-trying-to-connect-with-sql-2005-linked-server/m-p/22190
0
heenadosaAuthor Commented:
rr_miles,  my ultimate goal is 4. on your list.  "4) Transfer MAS90 data to SQL tables when MAS90 is updated. (Backup, Reporting Analysis, Integration with other systems)"
0
rr_milesCommented:

Then you need accomplish two things. 1) setup your SQL tables (obviously) and 2) transfer the data. Let’s focus on the second for a moment. Let’s use AR Customers as an example. Consider what update cycle you want to use to transfer the data from MAS90 to SQL. If you want to do it immediately (“real time”) when a Customer record is changed or is some hourly/daily interval acceptable. Remember a Customer record is also updated when an invoice or cash receipt is posted. Will these updated MAS90 fields be needed for the SQL table(s).  Add to this any SQL customizations.

Usually a Master Developer is required to intercept file handling do it in “real time”. I have done it in real time using Windows/VBS without messing with the Master Developer utilities or solutions. The latency from MAS90 to SQL was about 5 seconds (my choice).

These are the major considerations. This is why I suggested the DSD utility may be a good fit for your project. It will handle SQL setup and real time data transfer for you. If you have less money than time, a hand crafted solution using the Windows Scheduler is do-able. Scheduling also works in a non Windows environment.
0
rr_milesCommented:
BTW, if you don’t need custom or “real time” transfers, consider DTS (sql2000) or ISS (sql2005+) to transfer the data. Both use the MAS90 ODBC driver. It will allow you to create custom scripts using a wizard. This is NOT a “real time” solution, although is very fast and inexpensive (free?) to implement.
0
heenadosaAuthor Commented:
zephyr hex: I have been able to create the linked server and found that MAS90 has over 900 tables.  I need to transfer the structure of these tables to SQL and then start sending the data and any changes made to the data at a few second intervals if not real time.

rr miles: The DSD solutions is way too expensive.

Sorry for taking so long to get back.
0
rr_milesCommented:
So, we can do it by hand. Here is a PDF of my article to get data from MAS90. Review it and test the code on your system. There will be more to follow.

ODBC-Final.pdf
0
heenadosaAuthor Commented:
rr miles, thank you.  I will try it out this week.
0
rr_milesCommented:
Sone Notes and some background with an example.

First, MAS90’s ODBC limitations. The ODBC driver as shipped from Sage is Read-Only. You can find workarounds on the Internet, although these workarounds can and do cause errors when used with MAS90 and are not recommended.

MAS90’s ODBC driver is available on every computer that can login to MAS90. If a computer can login to MAS90, the ODBC driver is already installed and working. MAS90 itself does not use ODBC for data access, but its reporting sub-system does. The standard DSN is called SOTAMAS90. It can be viewed using Windows ODBC Administrator.

MAS90’s ODBC driver enforces the standard MAS90 user login security. Login by the ODBC driver is always required. All MAS90’s standard company and module rules apply. The login requires a company code, user code and password. Your MAS90 administrator can supply you with these. Login information can be passed to the driver by a script. Scripting the login may not be approved by your system administrator, so be sure to check your company’s policies. If the login information is omitted to the driver, an interactive logon box will be presented to the user. If you pass incorrect login information from a script to the driver, an interactive logon box will be presented to the user.

How do we know what data is available from MAS90? Within MAS90, the files and their fieldnames are documented. This is sometimes referred to as the TRSG. You can view the TRSG by looking above the Module’s menu in the left pane.  There is an item called Resources. Selecting it will bring up an option in the right pane with “File Layouts and Program Information” Selecting it will display a Windows Help style module. In the left pane is a list. One of the options is “File Layouts” Double click it to get a list of modules. Double click the AR module. A listing of the tables should be expanded in the left pane. Scan down to AR_Customer. Clicking it will display a listing in the right pane. Every file is documented this way

Attached is a complex script to demonstrate how to move MAS90 data to SQL.

mas2sql.pdf
0
heenadosaAuthor Commented:
Thank you everyone for posting all these solutions.  Unfortunately none of them worked the way we needed them to.  So we found a very good solution.  We created a new ODBC connection into MAS90 data and then used Inaport to create create give us details of the structure of each table.  Used SQL Query to create these tables.  Used Inaport to import the data into the tables.  It has worked very well so far.  If want to see Inaport, go to www.inaplex.com.
Thank you again.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Tax / Financial Software

From novice to tech pro — start learning today.