Connecting to a Sage MAS ERP 90 Database as a Linked Server within MS SQL Server 2008

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 a Sage MAS ERP 90 Database as a Linked Server within MS SQL Server 2008

  • Comments 9
  • Likes

A Linked Server within MS SQL Server is type of 'alias' within the SQL server that points to an external data source rather than a SQL Server database. This external data source can be in another RDBMS like Oracle or almost any other data system that can be accessed by OLE or ODBC--including Sage ERP MAS 90. If you have a background of using Access then you can think of a Linked Server as similar to the MS Access feature of a "Link Table."

But why do I want to use this in Sage CRM?

With a linked server, you can define very simple views that allow data from an ERP system like Sage ERP MAS 90 to be retrieved, joined and combined with Sage CRM Data.

Within an integration some data may be synchronized.  See the article "Integration with External Systems and the Synchronization of Application Data".  

The Sage ERP MAS 90 Extended Enterpise Suite moves data from Sage ERP MAS 90 to Sage CRM relatively easily. However, duplicating data on both the ERP and CRM servers is not always the best solution and the challenge for any developer, since large amounts of data could be moved 'just in case', is to predict what subset of data a customer may need.  So creating on-demand access to realtime data is part of the tool set that a Sage CRM developer needs. 

In my previous article "Connecting to an External ODBC database e.g. Sage ERP MAS 90", I discussed how a connection can be defined in Sage CRM to the Sage ERP MAS database and individual tables.  The data connection allows the ERP data to be accessed easily using the Sage CRM API objects.  Meta Data definitions can be stored that allow screens and lists to be used in ASP and .NET assemblies to access the data.

BUT it doesn't provide a way of defining a view that straddles the two data sources.  And without being able to define a view we can't create groups or reports.

A linked server provides us with more options.

And what is the possible downside?

When absolute, best possible performance is required, local data will out perform a linked server.  The physical link between the Sage CRM SQL Server and the remote Sage ERP MAS data might be slow.

Note:  The adoption of SData as a communication mechanism between different Sage products will allow Sage CRM and Sage ERP packages to exchange their data as very efficient HTTP requests and responses.  You can read about SData at http://sdata.sage.com. For an introductory article that explains how data can be pulled from Sage CRM and consumed by another application such as a Sage ERP package then please see "SData Provider introduction". 

To Create a Linked Server

There are other ways to create the Linked Server definition but I am only going to mention the method I found the easiest.

Open the MS SQL Management Studio and within the object explorer expand the Server Objects. Right click on Linked Servers

Within the following screen you will have to provide the information below:

  • Linked Server:  e.g. "MAS90_Link"  - the name you wish to use the identify the ERP server.
  • Server Type: Other Data Source
  • Provider: Microsoft OLE DB Provider for ODBC Drivers
  • Product Name: e.g. "MAS90Link" - as above
  • Provider String:  Driver={MAS 90 4.0 ODBC Driver}; UID=MAYES; PWD=; Directory=C:\Program Files\Sage Software\ERP Server\MAS90; Prefix=C:\Program Files\Sage Software\ERP Server\MAS90\SY\, C:\Program Files\Sage Software\ERP Server\MAS90\==\; ViewDLL=C:\Program Files\Sage Software\ERP Server\MAS90\HOME; Company=ABC; LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer

Note:  The Provider String may at first seem a little complex but the elements are quite easy to understand it you spend some time to seperate them into different statements seperated by a semi-colon.  UID=MAYES; PWD=; are the ERP user name and password to be used within the connection.  All others paths need to be checked to ensure they are correct for your install.  If you have already set up an ODBC System DSN then you can get some of this information from the Connection string button on the debug screen.  See the article "Connecting to an External ODBC database e.g. Sage ERP MAS 90".

Once you have saved the Linked Server, the tables will be visible under the Linked Server catalog.  

You can try and access the data using the fully qualified four-part identifier syntax. 


select * from [ServerName].[database name].[user name].table name;
 

e.g. 


select * from mas90_link...ar_customer;
 

If you find that you get an error such as

An invalid schema or catalog was specified for the provider "MSDASQL" for linked server

This may mean that you need to make sure “level zero only” is checked on the MSDASQL provider.

 

You should then be able to run queries such as this:


select itemcodedesc, * from CRMmas90.dbo.Company, mas90_link...vsaleshistory
where comp_mas_customerno = customerno;
 

Because you can now access the Sage ERP MAS 90 data using fully qualified SQL statements you can build views that in turn allow reports and groups to be built.

For a more complete discussion see the article "Building Cross Database Views for Reporting".

Comments
  • Just a heads up... I was trying to get this to work and it would just hang forever.  I then stopped/started the sql service and it worked perfectly.   Thanks!

  • That's a good tipe to know.

  • What is the best workaround for creating a linked server on a 64-bit SQL to the 32-MAS ODBC driver?

  • John

    I don't know - I don't have MAS on my machine anymore (and I'm still 32bit).  But I have seen references to making sure the correct admin tools are used

    support.microsoft.com/.../942976

    And there has been some discussion of this on the MAS forums e.g. community.sagemas.com/.../50360 and

    community.sagemas.com/.../58396

  • Thank you for this article that helped me with a customer request.

  • That's my pleasure!

  • This helped me alot to make a linked-server with my BOB50 db.   Maybe a points of attention : the collation settings were very tricky.  i had to set collation compatible to false, user remote collation to true and collation name needs to be empty.

    Thanks !

  • Kurt

    Good to hear and thanks for the tip.

  • I got the LinkedServer query to return records once, and ever after, it just hangs forever.  I read above that someone tried a restart of SQL Server, does anything need to be done on the MAS end as well?