How do create a linked server to SQL Anywhere 16?

[Former Member]

I need to create a linked server in SQL Server 2012. What parameters would I use connecting to the demo database suing SAOLEDB.16?

[VolkerBarth]

Have you had a look at these pages in the v16 doc? They contain a sample setup for the SA 16 demo database:

• Microsoft Linked Servers
• Setting up a Linked Server using an interactive application

They relate to MS SQL 2005/2008 but I guess the setup should work with MS SQL 2012 as well. Note: Though you may use the generic MSDASQL driver it's recommended to use the builtin SAOLEDB driver.

In case your setup does not work yet feel free to tell what connection strings and properties you have used so far...

[Former Member]

The link you provided is perfect. There is a slight error in the stored procedure adding a linked server that needs a fix. Using MSDASQL is not an option as there is no x64 bit version for Windows 7. There was another suggestion here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/46dc0747-4006-429a-85f0-fd976d75bae9/is-ther... But it is not appropriate form my set up.

[VolkerBarth]

That seems a strange contrast to the quote from the TechNet article "Data Access Technologies Road Map" cited by Breck:

MSDASQL: The Microsoft OLE DB Provider for ODBC (MSDASQL) is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. MSDASQL is an OLEDB provider that connects to ODBC, instead of a database. MSDASQL ships with the Windows operating system, and Windows Server 2008 and Vista SP1 were the first Windows releases to include a 64-bit version of the technology.

On my Windows 7 SP1 Professional 64-bit version, there certainly IS a 64-bit MSDASQL driver.

[VolkerBarth]

There is a slight error in the stored procedure adding a linked server that needs a fix.

So what is that error? - Note that you can comment on the documentation immediately in DCX so you may leave your suggestion there...

[Former Member]

I find only one odbc.dll in my Windows directory but that is odbc32.dll in C:\\Windows\\SysWOW64\\odbc32.dll. I am working on a Windows 7(x64bit) Ultimate laptop. I have had a discussion going on in MSDN forum as well and at least one of them confirmed. I am writing a post to my blog http://hodentekMSSS.blogspot.com

[Breck_Carter]

The code shown in Download Via Linked Server is for SQL Anywhere 11, but it may give you some ideas.

[Former Member]

Hi Breck,

Thanks. I did try this. It does not seem to work. On a Windows 7 (x64bit) machine there is no Microsoft OLEDB for ODBC (MSDASQL). There are samples from Sybase There are two providers on SQL Server 2012 that can be used for creating linked servers: MSDASQL SAOLEDB.16

The first choice will not work. There isn't enough documentation for using SAOLEDB.16. It is not super urgent for me, plain academic interest.

[Breck_Carter]

> The first choice will not work.

Be wary of bold statements 🙂

Microsoft lists MSDASQL as the "Microsoft OLE DB Provider for ODBC" in the Help topic for SQL Server 2014 sp_addlinkedserver.

According to Microsoft's Data Access Technologies Road Map "MSDASQL ships with the Windows operating system".

Check this location on Windows 7...

C:\\Program Files\\Common Files\\System\\Ole DB\\msdasql.dll

[VolkerBarth]

FWIW, on 64-bit systems, there should also be a msdasql.dll in the 32-bit program files directory, i.e.

C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdasql.dll

Nevertheless, since SQL Server 2012 only comes as 64-bit software AFAIK, you will have to use the 64-bit OLEDB drivers for linked servers, no matter whether you will use the MSDASQL or SAOLEDB drivers...

[Former Member]

SAOLEDB is perfect for this. There is no MSDASQL(x64) on Windows 7 OS.

[Breck_Carter]

> There is no MSDASQL(x64) on Windows 7 OS

That is not true... please stop saying it.

It is located here...

C:\\Program Files\\Common Files\\System\\Ole DB\\msdasql.dll