Connecting to commercial software

[philfer]

Hello,

I want to use an SQL Server database to connect to our commercial accounting software overnight and take a snapshot of the data.

This will be used to run stored procs and save "extracts" as Excel files for people to use for various reporting the next day.

Firstly, is this possible!!!

Secondly, would anyone be able to give me any tips/advice about how to get started with this exercise or where I can find online resources to help with this.

As always, I appreciate your help
Phil

 

[drlex]

Phil,
It should be possible; it really depends on the commerical software package that you wish to connect to. Care to name it, then you're more likely to get specific advice?
You might also post this Q in any support fora for that particular package. (There is an outside chance that to prevent customer 'interference' or upsell report features or even discourage migration, the package prevents reads with encryption or another security feature.)

FWIW, I use SQL server in such a fashion to connect to our trading system which utilizes a PROGRESS database. The communication is done with a third party software utility that allows interrogation through an ODBC link. The read is done in the few hours between backup of the trading db and the start of live trading. The advantage is that live performance is not impacted and it is easy to reconcile from a snapshot rather than a moving target. Fortunately, the value of live data is not so great as to require live analysis and the problems that would bring for us.

soi là, soi carré

 

[simian336]

The first step would be to find out what type of database system is being used by the Accounting Software and find out if drivers are available for that package.

Simi

 

[JoeAtWork]

As drlex mentioned, how you get the data entirely depends on what Accounting software you need to connect to.

And just getting to their database is not enough - you need to know where the data you need is located, and how to properly filter it. The data may be spread in several tables, so you need to know how those tables should be joined. Also, it is likely you need to filter data to get the "real" data. For example, the Accounting package may "soft delete" records, that is they aren't actually deleted, but rather a flag field is set to True. The Accounting software knows to ignore these records. This is an example of a "business rule" that you would need to know to get the data you expect. There are likely many such "business rules".

So you will probably need to ask your questions in a forum dedicated to that particular Accounting application. If it happens to be AccPac, the TekTips AccPac forum has some very knowleadgable people (its what brought me here in the first place).