Connecting to an External ODBC database e.g. Sage 100cloud

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Connecting to an External ODBC database e.g. Sage 100cloud

  • Comments 11
  • Likes

I needed to establish a connection to the Sage 100cloud database to allow me to check details about a customer record within the integrated system.  A direct connection to an ODBC database is a very useful way to allow realtime queries to be run against the ERP data from within the Sage CRM interface.  

Full integration that synchronizes data updates with an external ERP package is not a trivial task.  Some of the things that a developer has to consider are discussed in the article "Integration with External Systems and the Synchronization of Application Data".  

Although I am working with the Sage 100cloud I am going to keep things as simple as possible.  I only need to open the connection to view data and I am not proposing to by pass business logic; I would not use the ODBC connection for data manipulation only for reading. 

Note:  The Example Driver I am using  (MAS 90 4.0 ODBC Driver) is READ ONLY.  Any attempt to use the DSN to update data will result in an error.

Sage CRM can connect to wide variety of database types.  I have discussed this previously in the article "Linking to 3rd Party Databases".  

Sometimes there can be challenges to linking to external databases because of quirks such as the Database being case sensitive.  See the article "Database Case-Sensitivity and Connecting to an External Case Sensitive Database" for a discussion of what to do.

A Data Source Name (DSN) is required in the setup of ODBC in order to specify the connection information for a database server. Sage CRM uses the information contained within the DSN to locate and log on to a database.

To view the available ODBC DSN on the Sage CRM server you would need to open the windows control panel folder and then view administrative tools.  The short cut to manage Data sources can be accessed.

The version of Sage 100cloud I was using provided a User DSN called SOTAMAS90.

User DSNs work only for a specific user on a machine. Therefore, if someone other than the person who created the user DSN logs onto that machine, then the User DSN will not show up. This DSN is stored in the Current User section of the Registry.

I needed to allow Sage CRM to use the ODBC connection so I  had to create a System DSN.  A System DSN works for anyone using that system. For example, no matter who logs onto the machine, system DSNs can be seen by all users, and they are stored in the Local Machine section of the Registry.

To create the System DSN I had to do the following:

I clicked on the System DSN tab and then click the "Add..." button.  This opened a dialog box that asked me to select the driver to be used for the new Data Source.  I selected the "MAS 90 4.0 ODBC Driver" and then clicked finished.

I had to provide the basic information.  The Data Source has to be given a name, a description and I need to specify the path to where my providex.ddf file had been created.  I called my DSN 'myMAS90'.

I then provided the Logon information that I was using.  I defined the Company Database within Sage ERP MAS I was going to connect to, the User name and password to access the system.  I made sure that the Company code (e.g. ABC) was the correct case.

I then made sure the information within the options tab was correct.

  • Prefix for data files:  C:\Program Files\Sage Software\ERP Server\MAS90\SY\, C:\Program Files\Sage Software\ERP Server\MAS90\==\
  • Paths to View DLL:  C:\Program Files\Sage Software\ERP Server\MAS90\Home\

The remaining information I left as default, but please refer to the Sage ERP MAS 90 documentation for more information regarding these.

Finally to be sure that my settings were correct I tested them using the "Test Connection" button on Debug tab.

Note:  I found the information provided by the Connection string useful when setting up a linked server.  But that is another article.

Creating the Connection in Sage CRM

Once I had the System DSN connection created it was very simple to establish the connection.

I logged on to Sage CRM as the System Administrator and navigated to 

Administration -> Advanced Customization -> Tables and Databases

I then clicked the 'New Database Connection' button.

The Database name (and description) is the name of the System DSN created earlier.  I provided a valid User Name and Password for the ERP connection.

Once the connection definition has been saved and the connection to the database made.  I could then add a link to a table.

I clicked the 'New Table Connection' button and defined a connection to the ar_customer table.

This table is now able to be further described using Sage CRM meta data.

The ar_customer table can now be referenced in code within the screens and table scripts e.g.

[code language="javascript"]
var myRecord = CRM.FindRecord("ar_customer","customername like 'Bres%'");
[/code] 

List blocks and screens can now be defined that can be referenced in ASP pages and in .NET assemblies.

Note:  The odbc driver demonstrates case sensitivity for data but not for table names.  So for example

  • select * from ar_customer where customername like 'bres%'

would not find any data but 

  • select * from ar_customer where customername like 'Bres%'

would.

and 

  • select * from ar_customer where CUSTOMERNAME like 'Bres%' 

works just like the previous example.

 

Comments
  • I am trying to do this very thing but I have one small problem. I have a 64bit SQL Server. It does not want to play with the 32bit MAS90 ODBC driver. I have looked for a solution besides installing 32bit SQL Server.  I heard I could setup the 32bit ODBC driver in the 64bit registry. Does anyone have a work around?

  • This article maybe helpful

    community.sagemas.com/.../53018

  • I follow this article exactly on a test server where MAS, CRM, and SQL are on the same box, I had no issues.  I then attempted to this the live environment, SQL, MAS, and CRM are all on separate servers.  I set up the DSN just as described in the article and the connect test passed.  In CRM i put in the settings to connect to the database.  I then tried to create a new table connection and received the following error:

    SQL error:  Table may not exist in database OR table name may need to be enclosed in square brackets []

    Enclosing the table name in [] did not fix anything.  Any thoughts on how to resolve this error?

  • Was this resolved?  I have run into the same error.

  • Hello I am running SAGE CRM 7.1.d.4 s and have the same error as Estranda, and Alan.  I have been able to do this in several other installs.  the only difference, I think is this is a test box running the MSDE.   Anybody found an answer to this one.

  • I found my issue, my Iusr in IIS was a local admin and did not have full permission to the MAS 90 directory on a different machine.

  • Thanks for the very usefult tip!

  • Hi...

    I am trying to do this.  I created the odbc connection and tested and it was successful, but when I go to connect a new database in Sage CRM and try to save it, I get a "unable to connect to database" message.  I don't know what to do.  Thanks.

    B

  • I'm running Version 7.1 SP2-hotfix.3 S. Our ERP server is on a different host than CRM. The ODbC connection will not connect through the web portal. The ODBC works windows to windows but I need it to go through the web.  I saw where someone said the IIS user permissions were different but I have full admin rights on both machines and still no luck. Has anyone a detailed solution for the settings to enable this? I really need to get this working.

  • Hello,

    I hope you all are doing good. I have a question

    After connecting the CRM with Mas100 could the visitors see invoices, sales orders. If not , how can I establish this goal?

    Regards,

    Sareen

  • The integration with Sage 100 ERP (formerly Sage MAS 90) offers the ability to surface ERP data directly within Sage CRM.    I don't work with Sage 100 ERP so I am not an expert.  You can find some information about the integration here:  na.sage.com/.../Sage_100_ERP_CRM_spec.pdf

    But the ERP is in a database and Sage CRM is very good at connecting to external databases and allowing data to be surfaced.  This article is just one of several that explain how data in a back office system can be surfaced within Sage CRM.