Linked servers offer the following advantages as noted in MSDN documentation:
•The ability to access data from outside of SQL Server.
•The ability to issue distributed queries, updates, commands, and transactions on heterogeneous
data sources across the enterprise.
•The ability to address diverse data sources similarly.
There are two ways to create Linked Servers in SQL Server 2012.
- Using SQL Server Management Studio(SSMS)
- Using the sp_addlinkedserver stored procedure(T-SQL)
In this post a Linked Server to Sybase(SAP) SQL Anywhere Server 16 will be created using SSMS
in SQL Server 2012 Express. Both the servers are installed on Windows 7 Ultimate
(x64bit) Toshiba Laptop computer.
Required information for SQL Anywhere 16
If you do not have SQL Anywhere 16, you can download the free developer version here after
registering:
http://sqlanywhere-forum.sap.com/questions/15828/its-here-sql-anywhere-16-developer-edition--
download
When you install SQL Anywhere 16 on your computer you can access the servers from the shortcut
shown here:
When you double click the
Network Server (64-bit) you would be starting the server. The server details can be seen on the starting screen:
From this server you get the information:
Servername: demo2
Port on localhost: 2638
Name of database: demo.db
SQL Anywhere 16 installation also brings in ODBC drivers, both x32 bit and x64bit. On a Windows
(x64) machine you can access both of them in separate ODBC Manager screens. To access
their shortcuts enter ODBC in the Start | Search screen. From here you can choose either of them.
However if you start from Control Panel | System and Security | Administrative Tools |ODBC Manager the x64bit version is displayed as shown:
During SQL Anywhere 16 installation a
System ODBC DSN - SQL Anywhere 16 DEMO is also installed as shown.
Creating a linked server in SQL Server 2012 Express
It was not clear that a linked server can be created in SQL Server 2012 Express. Not a word about
Linked Servers in the features supported by SQL Server 2012 was available:
http://msdn.microsoft.com/en-us/library/cc645993(d=printer,v=sql.110).aspx#SSIS
However under
Server Objects node in SQL Server you can find Linked Servers with the following providers that allows you to create linked servers with other vendor databases such as SAP, Oracle, DB2 etc.
In order to begin the process of creating a linked server right click
Linked Servers node and click
New Linked Server...
This opens up a window with three pages (
General, Security and Server Options) as shown.
General Page:
In the General page you provide a name (your own: Herein Test_SAP) for the Linked Server.
For provider the default is Microsoft OLE DB Provider for SQL Server. You can click the drop-down
and select Microsoft OLE DB Provider for ODBC or Sybase(SAP)'s SQL Anywhere OLE DB Provider
16 (SAOLEDB.16) from the drop-down list shown.
Although Microsoft OLE DB Provider for ODBC (aka MSDASQL) can be used for creating a linked
server, you will not be successful because there is no (x64) version of this for Windows 7 (x64). If you are on a Windows Server 2003, or a Window's Vista machine there are URLs on the Internet where you can download them.
Going forward, the post uses the SAP's SQL Anywhere OLE DB Provider 16.
Choose the
SQL Anywhere OLE DB Provider 16 from the drop-down list.
The
Product name is optional and you can type in anything.
For the
Provider string use the following:
Server: demo16; dbf: demo.db --Info from the server earlier
For the
Data source, use the System ODBC DSN:
SQL Anywhere 16 DEMO -from ODBC Manager
For
Location (which only gets enabled after you choose SQL Anywhere OLE DB Provider 16) enter the following:
Localhost:2638 -- from demo 16 screen
For
Catalog: enter
demo
The completed screen of the
General page is as shown:
Security page:
In the Security page enter the credentials to access the sample database
demo (login:
dba,
password:
sql--case sensitive) on demo 16 server as shown
Server Options:
In the
Server Options page make entries as shown here:
You have to enable
RPC and
RPC-Out options (I am still studying this) as shown above.
Allow Inprocess option for the provider:
You also need to enable
Allow Inprocess option for the SAOLEDB.16 provider.
Access the SAOLEDB.16 provider from Linked Servers | Providers node. Right click SAOLEDB.2 and open the property page as shown and enable 'Allow Inprocess' option.
Now this completes the creation of a linked server. Click
OK on the
New Linked Server page.
A new linked server
Test-SAP will be created in the Linked Server's node displaying the objects in the 'demo' database on the SQL Anywhere 16 server demo16 as shown.
The first two tables owned by DBA are not in the sample database that you get after installing the
SQL Anywhere 16 server. These were created during another study to connect SQL Anywhere 16
server to
OpenOffice and
LibreOffice programs. These can be accessed here:
http://hodentek.blogspot.com/2014/02/libreoffice-42-does-better-than.html
This post greatly benefited from discussions and suggestions from moderators (Volker Barth and Breck Carter ) on the SAP forum.
Here are some helpful links;
MSDASQL:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/46dc0747-4006-429a-85f0-
fd976d75bae9/is-there-a-msdasql-64-provider-on-windows-7-ultimate-x64-bit-os?
forum=sqldatabaseengine#cb4dc072-899e-4689-879f-11312c920c7b
Link to Linked Server for Sybase IQ 16 on Solaris:
http://arbime.wordpress.com/2013/11/14/ms-sql-2005-create-a-link-server-to-sybase-iq-16/
Get all my linked server articles here:
http://hodentek.blogspot.com/search?q=linked+server
Get all my SQL Anywhere articles here:
http://hodentek.blogspot.com/2013/08/looking-at-sql-anywhere-after-long-break.html
