Connect to MAS200 via ODBC and transfer data to hosted server 1

[jbdeveloper]

Hello. I wrote a hosted web application (ASP.NET) for a customer who now would like to use/display some information from their MAS200 on the web application.

I have done a bit of research and found that I can access the MAS200 data read-only via an ODBC driver on the MAS200 server. I am still working with their IT person to get remote access to the MAS200 server - but I want to be ready to roll once granted access.

I think these are the steps I need to take. Can you please let me know if I am on the right track, am I missing something, is there a better way, etc..?

1) Identify which tables and specific data my users want to extract from MAS200 (is there a data dictionary of some sort to map the user data to the tables/fields I will need to extract?)
2) I am thinking I will need SQL Express or some sort of simple DB tool installed on the MAS200 server to query and temporarily hold the extracted data.
3) Create ODBC Data Connection to the MAS200.
4) Query and store data on MAS200 server.
5) Determine best way to transfer data from internal MAS200 server to hosted server - any suggestions? I was thinking a scheduled console program that ran the query and ftp'd a file...but maybe SQL has some tools that can do all this directly from db to db?

Any help or suggestions on any part of this process would be helpful. This is the first time I have attempted to access MAS200 as well as transfer data between an internal server to a hosted server.

Thank you in advance.

 

[BigLouie]

You should post this on the Sage board for MAS which can be found at community.sagemas.com There are a number of other developers who have discussed topics along this line. You can find the table layouts in the MAS install by going to Resourcs on the menu in MAS.

 

[sactowngirl]

A good DB tool that I use is WinSQL. This tool will show you all the tables from MAS after you select the connection string, Company, User and PWD. To query the data, you use a language pretty close to T-SQL... only few things are different. But normal queries are like select * from SO_SalesOrderHeader.