Share via

Linked server to a Sybase / SQLAnywhere database

Darrell Burns 141 Reputation points
2021-09-07T22:30:13.29+00:00

I've been beating my head against a wall trying to create a linked server to a SQLAnywhere 11 database from SQL Server (12.0). Can't seem to get the parameters right.
Since I can't seem to get my hands on a copy of SAOLEDB.12 I'm stuck using MSDASQL as the provider. But I can't get past this error message...

OLE DB provider "MSDASQL" for linked server "myserver" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

EXEC master.dbo.sp_addlinkedserver @Testta =N'MyServer'
,@srvproduct=N'SQL Anywhere'
,@provider=N'MSDASQL'
,@datasrc=N'SQLAnywhere64'
,@provstr=N'host=ServerName;dbn=DatabaseName'

ODBC System Data sources...

129987-datasources.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,948 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,566 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Voon Liew 1 Reputation point
    2022-07-28T17:23:24.21+00:00

    The OLE DB provider is included in the SQL Anywhere 12 client install but you need to manually register the driver as detailed here:
    https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbprogramming/pg-oledb-registration.html

    • Open a command prompt and navigate to %programfiles%\SQL Anywhere 12\Bin32 (for 32 bit)
    • Run "regsvr32 dboledb12.dll" and "regsvr32 dboledba12.dll"

    Refresh the providers list in SSMS and SAOLEDB.12 should now be there.

    Add the linked server using a command like this:
    EXEC master.dbo.sp_addlinkedserver @Testta = 'AnyName', @srvproduct='AnyName', @provider='SAOLEDB.12', @location='10.0.1.1:2638', @provstr='server=SaServerName', @catalog='InitDBName'

    As per the following documentation page you will also need to add a security mapping (DB login), RPC server setting and InProc provider settings :
    https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbprogramming/pg-oledb-linked-servers.html

    0 comments