Greetings 90 Mind-ers. This post/discussion is from a request that @John Wilder made in another thread. He bolded one thing in particular: "How can we get more relevant data out of Sage 100 and into Sage CRM without third-party plug-ins or costly consulting resource."
Below is a simplified walkthrough explaining "how" to get you started. The use cases beyond this point are pretty extensive and do not require any additional add-ins to make happen. The video is raw (no edits), but happy to clean it up if needed.
Estimated Time to Replicate: Depending on your familiarity with ODBC connections and SQL, this could take you less time than the video illustrates or slightly longer. Below is a link to the video on YouTube, and below that are the pre-requisites listed before you can make this happen.
Video Link (9:12 in duration): https://youtu.be/NfhQZ10qF0w
Pre-requisites:
- Sage 100 Standard or Advanced must be installed. If you're using Sage 100 Premium, this video is useless as you can create direct queries to the Sage 100 data.
- (needed if your SQL Server is a 64-bit instance) "64-bit ODBC driver" installed on the server that hosts your SQL Server instance for Sage CRM from the Workstation installer menu.
- A Sage 100 user must be configured with the proper securities to access data in Sage 100 for the company code you wish to integrate.
- UNC or file path to your Sage 100 data noted (comes in handy later)
Resources needed in the video:
- When configuring your system DSN (in the "Data Sources (64-bit)" system application), you'll need to know the following information from each tab. The "Example" values in each take into account the default installation location if the Sage 100 application was installed in the default location on the server:
- "Basic":
- "Database Directory:" value : [Sage 100 Directory]\MAS90\
- Example: C:\Sage\Sage 100 Advanced\MAS90
- "Server":
- Generally speaking, this tab isn't used unless your SQL Server database engine resides on a server that's different than where Sage 100 is installed. If that is the case, then there's more to configure here that this guide doesn't cover.
- "Logon":
- Company Code: the Sage 100 company code you want to pull data out of Sage 100
- Default User ID: a user configured with correct security to access Sage 100 data
- Password: the password for that user (presuming one exists)
- Options:
- "Prefix for data files:" value: [Sage 100 Directory]\MAS90\SY\, [Sage 100 Directory]\MAS90\==\
- Example: C:\Sage\Sage 100 Advanced\MAS90\SY\, C:\Sage\Sage 100 Advanced\MAS90\==\
- "Path to Views DLL:" value: [Sage 100 Directory]\MAS90\Home\
- Example: C:\Sage\Sage 100 Advanced\MAS90\Home\
- Set additional options to be the following on the "Options" tab:
- "Dirty Read:" - set to true or "checked"
- "Burst mode:" - set to true or "checked"
- "Strip training spaces:" - set to true or "checked"
- "Cache Size MB:" - set to a value of 4 for a minimum, but the higher this is set, the longer any query will remain in memory (don't get carried away). I suggest 16 or 32 for a maximum value here.
This will get you started with the setup. From here, we can start bringing over additional data that isn't available via the native integration. UDFs, standard Sage 100 AR_Customer fields, etc., can now all get brought from Sage 100 into Sage CRM. If you would like to cover some sample queries or additional use cases of using the Linked Server to benefit your visibility of Sage 100 data in Sage CRM, please feel free to comment below.
I can add to the YouTube playlist as we get more engagement on the topic of Sage 100 and Sage CRM - presuming there's interest from the group on further enablement.
------------------------------
Basil Malik
e:
basil@ss-adv.comp: (918)600-8700
"Whatever you are, try to be a good one."
------------------------------