Creating a linked server to SAP/Sybase SQL Anywhere 16 server in SQL Server 2012 Express

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