Mikey - when you say "install SQL into Sage", what are you trying to accomplish?  The base program runs under Pervasive SQL and you can run the SQL replicator to reproduce the data into MS SQL Server..  

Hi, thank you for replying. I am new to sage 300 CRE. I used use MSSQL and mysql alone. Now I am asked to use sql to form report in Sage 300. So I am not quite sure how to use that on sage. I thought there is an specific version of sql that was used under sage 300. I searched online and I saw the  SQL replicator. Would you give me some guidence on how you normally use PSQL or MS SQL in sage? Thank you!

So SQL replicator is just the tool that can reporduce or transform the data into MS SQL? Does that mean if I want to do some sql query commands on datasets, I will still use MS SQL server?

So if your intent is to use SQL for reporting and you don't want to replicate, you can work directly with the Pervasive SQL database using the 32 bit ODBC driver that ships with the program. This is how Crystal reports accesses the system.  The base ODBC driver works well for Excel, Access, Word and Power Query.  The Office Connector product from Event1 is also a fine tool, especially if you're doing financial statements plus is also has a built in SQL editor if you prefer to write your queries by hand (same as you can do in Excel but far friendlier). 

If you really want to stick with MS SQL, I assume the replicated database option may be the best choice, however I personally know few people using it and have limited experience with it.

Thank you so much for the suggestions. I will talk with my collegues. 

Hi Mike. I check the report my college wrote. It seems they are using the sql editor in excel. I am not 100% sure whether they are using the office connector. Would you mind take a look at the report? If it is confirmed that it was written using excel office connector, then I have track to follow. Thank you so much!

Another option is to look at AnterraBi platform.  All build out for Sage users - love this idea

https://www.youtube.com/watch?v=DzLF-AeSPf8

Mikey - feel free to email to me at [email protected] and I'll take a quick look and tell you what you have.  

Thank u so much! I feel so grateful!

Hi Mike. I think my collegue is using OBDC connect to the sql server to write the sql queries and report. I saw the queries  are in as following steps in tables in excel:

right click  table - table data properties- connection properties - definition. Then there is connection type: data: query, command type: sql. These are all shown in excel report. 

Sorry for my descriptions. Would you be able to understand what I am trying to express?

Another option depending on what you are wanting to accomplish s using Event1.

Thank you so much!

Thank you! 

Ok, that clears things up.  Sounds like your colleague wrote an ODBC query and embedded it in the Excel spreadsheet, either using Microsoft Query or hard coding the SQL code.  Event1 Office Connector would not attach that way.

This is the standard method of using native ODBC with Sage.  Under 32 bit ODBC you set up a DSN using the Sage ODBC driver that points to a specific Sage data folder.  You then write queries against the DSN in Excel using Data->Get Data->From Other Sources and typically using either From MS Query or From ODBC. 

Wow Thank you so much! I think you are totally right on this.

Hey Mike, I add you on linkedin. I just want to say thank you to you!