In this post, I would like to discuss how to setup a linked server between SQL Server 2008 R2 and MAS90 server database. The first step before setting up the linked server is to install the MAS90 driver and then configure the system DSN. Below are the screenshots that will guide a user to setup/configure system DSN.
1)Go to start–>run–>type odbcad32 and then hit enter
2)In the ‘ODBC Data Source Administrator’ choose the System DSN tab. In the System Data Sources click on Add button to add a new system data source. Select the MAS () 4.0 ODBC Driver as shown below:
3)Click on Finish and then enter the Data Source Name, Description and database directory as shown below. The datasource name that is put here will be the data source name in SQL Server Linked Server setup.
4) Enter nothing in the Server Tab:
5) In the Logon tab enter the company code, default user id and the password as shown below. This userid and password are the users of MAS90 database.
6)Enter the path locations of the ‘SY’ folder and the ‘MAS90==’ folder separated by a comma in the ‘String for prefix for data files:’ text area and then enter ‘Home’ directory location in the ‘String for Path to views dll:’ text area as shown below:
Check on Dirty Read(if you want to read uncommitted data), Burst Mode and Strip Trailing spaces. I selected the cache size MB: 4
7)Now go to Debug tab and click on connection string button and copy the connection string to a notepad and click on test connection button to test the connection as shown below. If the connection succeeds click on OK.
8)A new System DSN is now created with the details you provided.
9)Now Login to SQL Server to setup the Linked Server. In the SSMS expand the “Server Objects” folder and click on New Linked Server as shown below
10) Enter the linked server name and check source type as other data source. In the drop down select “Microsoft OLE DB Provider for SQL Server”. Enter the name for the Product Name. Copy the data source name from the DataSource name of ODBC driver setup and paste it for Data Source. Copy the connection string from the ODBC setup and paste it for Provider String as shown below. Now click on OK.
11)In the security tab click on add for the “Local server login to remote server login mappings”. Enter a local sql server user for the local login and MAS90 username for remote user and MAS90 password for remote password as shown below. Now, “For a login not deined in the list above, connections will:” section check the “Be made using this security context:” and enter the local sql server username and password for remote login and with password fields respectively as shown below.
12)In the server options tab change the values as shown below and click on OK button.
Now the link server is setup. Right click on the linked server that is created and click on test connection. If the connection succeeds you will see the following message:
Open a query window and run the following sql:
select count(*) from testlinkserver...ar_customer
You should be able to see the count or rows in the ar_customer table.
Please note: At the time of posting this article on the blog the Linked Server failed to establish connection with MAS90 if SQL Server version was 64-bit. To get rid of the issue I installed a 32bit Standard edition of SQL Server and the linked server was connecting fine with MAS90 without issues. There were no restrictions/limitations on the editions of SQL Server for establishing connections with the linked server.
Hope this helps