Run SQL Query on Sage 50 Database

Hi all,

I am trying to find out how to run sql queries on the Sage 50 database (wherever it is stored).

I have a Windows 7, with Sage 50 installed and I need to get to the database of it and run some sql queries.

How can I do this?
error77Asked:
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.

Bruce DenneySage 50 Consultant and IntegratorCommented:
Which Sage 50 is this, UK/CAN/US ?
Version number would also be useful
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
For UK versions you just use ODBC

The connection sting is


ConnectionString = "ODBC;DSN=" & DN & ";UID=" & UR & ";PWD=" & PW & ";DIR=" & DR & ";"

Where

This uses the real Version number not the year version number tacked onto  SageLine50v
Example for V2013
'DN = "SageLine50v19"

UserName
'UR = "manager"

Password
'PW = ""

The path to the Data files this has \ACCDATA on the end of it

'DR = "S:\ACCDATA"
0
error77Author Commented:
What software should I use to connect to it or does it create a connection that I can connect to via wamp web if I wanted to?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

error77Author Commented:
It's the UK version btw
0
error77Author Commented:
Anyone please?
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
Perhaps you should explain what you are trying to do.

Sage 50 can have a massive database and reading it over the internet is not really viable.
0
error77Author Commented:
Hi,

Well, basically I want to try to perform manual SQL queries from Sqge 50.

I don't want to have to build a report just write and run the sql query.

Hope this help

Thanks
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
You want to get data out of Sage 50 into what? to do what?

For example you can read data from Excel using SQL statements (via MS Query)

Similarly you can read data using ODBC from programming languages eg C, Visual Basic, php,Java, asp, Delphi..

They all work the same way, you open an ODBC connection and then use SQL to get data returned from the other database.
0
error77Author Commented:
What I'm I connecting to... I mean where do I find the data file from Sage 50 ?  Or do I need to export to csv and then connect odbc to that?
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
ODBC allows you to issue SQL queries against the Sage50 Database, as I detailed in my earlier response, you need to include the path to the sage 50 data directory.

Are you asking for the path to your Sage 50 Database files, so that you can put it in the ODBC connect string?

The instructions for finding the path to your Sage 50 Data directory are dependant on the version number.
If you look around in Help>About you should be able to find it somewhere.  If not give me the version number.
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
error77Author Commented:
Thanks again for your response.

The version number is: 17.0.15.208

Thanks again
0
Bruce DenneySage 50 Consultant and IntegratorCommented:
Go to help>about>System information

Look on the left side for Directories click on it
on the right you will see Data Directory and the path, right click and open folder to see the whole path.
0
Andy636Commented:
I have found a way to transfer all of the Sage 50 data on a schedule without any expensive connectors:

http://sysadmin2programmer.com/upload-sage-50-data-to-sql-server-sync-with-odbc
0
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
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.