Tuesday, July 08, 2014

Use Sybase(SAP)'s SQL Anywhere OLE DB Provider 16 to create a linked server in SQL Server 2012

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
  • Using the sp_addlinkedserver stored procedure
Microsoft SQL Server 2012 has two providers to connect to SQL Anywhere 16 from SAP (Sybase). They are,

MSDASQL - Microsoft OLE DB Provider for ODBC
SAOLEDB.16 - SQL Anywhere OLE DB Provider 16

When you install SQL Anywhere 16 the servers can be used for both architectures.,
Of the two providers MSDASQL cannot be used as no MSDASQL is available for x64bit and attempts to use it to configure a linked server results in failure.

However if you use SAOLEDB.16 you can successfully create a linked server.

For a detailed step-by-step explanation review the following link:

No comments:

DMCA.com Protection Status