Configuration of a Microsoft Linked Server using OLE DB

A Microsoft Linked Server can be created that uses the SQL Anywhere OLE DB provider to obtain access to a SQL Anywhere database. SQL queries can be issued using either the Microsoft four-part table referencing syntax or the Microsoft OPENQUERY SQL function. An example of the four-part syntax follows.

SELECT * FROM SADATABASE.demo.GROUPO.Customers

In this example, SADATABASE is the name of the Linked Server, demo is the catalog or database name, GROUPO is the table owner in the SQL Anywhere database, and Customers is the table name in the SQL Anywhere database.

The other form uses the Microsoft OPENQUERY function.

SELECT * FROM OPENQUERY( SADATABASE, 'SELECT * FROM Customers' )

In the OPENQUERY syntax, the second SELECT statement ( 'SELECT * FROM Customers' ) is passed to the SQL Anywhere server for execution.

You can set up a Linked Server that uses the SQL Anywhere OLE DB provider using a Microsoft SQL Server interactive application or a SQL Server script.

Note

Before setting up a Linked Server, there are a few things to consider when using Windows Vista or later versions of Windows. SQL Server runs as a service on your system. Depending on how the service is set up on Windows Vista or later versions, a service may not be able to use shared memory connections, it may not be able to start a server, and it may not be able to access User Data Source definitions. For example, a service logged in as a Network Service cannot start servers, connect via shared memory, or access User Data Sources. For these situations, the SQL Anywhere server must be started ahead of time and the TCPIP communication protocol must be used. Also, if a data source is to be used, it must be a System Data Source.

Set up a Linked Server interactively

For Microsoft SQL Server 2005/2008, start SQL Server Management Studio. For other versions of SQL Server, the name of this application and the steps to setting up a Linked Server may vary.

In the Object Explorer pane, expand Server Objects » Linked Servers. Right-click Linked Servers and then click New Linked Server.
Fill in the General page.

The Linked Server field on the General page should contain a Linked Server name (like SADATABASE in the example above).

The Other Data Source option should be chosen, and SQL Anywhere OLE DB Provider 12 should be chosen from the Provider list.

The Product Name field can be anything you like (for example, SQL Anywhere or your application name).

The Data Source field can contain an ODBC data source name (DSN). This is a convenience option and a data source name is not required. If you use a System DSN, it must be a 32-bit DSN for 32-bit versions of SQL Server or a 64-bit DSN for 64-bit versions of SQL Server.
Data Source: SQL Anywhere 12 Demo
The Provider String field can contain additional connection parameters such as UserID (UID), ServerName (Server), and DatabaseFile (DBF).
Provider string: Server=myserver;DBF=sample.db
The Location field can contain the equivalent of the SQL Anywhere Host connection parameter (for example, localhost:4444 or 10.25.99.253:2638).
Location: AppServer-pc:2639
The Initial Catalog field can contain the name of the database to connect to (for example, demo). The database must have been previously started.
Initial Catalog: demo
The combination of these last four fields and the user ID and password from the Security page must contain enough information to successfully connect to a database server.
Instead of specifying the database user ID and password as a connection parameter in the Provider String field where it would be exposed in plain text, you can fill in the Security page.

In SQL Server 2005/2008, click the Be made using this security context option and fill in the Remote login and With password fields (the password is displayed as asterisks).
Go to the Server Options page.

Enable the RPC and RPC Out options.

The technique for doing this varies with different versions of Microsoft SQL Server. In SQL Server 2000, there are two checkboxes that must be checked for these two options. In SQL Server 2005/2008, the options are True/False settings. Make sure that they are set True. The Remote Procedure Call (RPC) options must be set if you want to execute stored procedure/function calls in a SQL Anywhere database and pass parameters in and out successfully.
Choose the Allow Inprocess provider option.

The technique for doing this varies with different versions of Microsoft SQL Server. In SQL Server 2000, there is a Provider Options button that takes you to the page where you can choose this option. For SQL Server 2005/2008, right-click the SAOLEDB.12 provider name under Linked Servers » Providers and click Properties. Make sure the Allow Inprocess checkbox is checked. If the Inprocess option is not chosen, queries fail.

Set up a Linked Server using a script

A Linked Server may be set up using a SQL Server script similar to the following. Make the appropriate changes to the script before running it on SQL Server. You should choose a new Linked Server name (SADATABASE is used in the example), an optional data source name (SQL Anywhere 12 Demo is used in the example), an optional provider string (N'host=localhost:4444;server=myserver;dbn=demo' is used in the example), and a remote user ID and password.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server=N'SADATABASE', 
     @srvproduct=N'SQL Anywhere', @provider=N'SAOLEDB.12', 
     @datasrc=N'SQL Anywhere 12 Demo',
     @provstr=N'host=localhost:4444;server=myserver;dbn=demo'
GO
EXEC master.dbo.sp_serveroption @server=N'SADATABASE', 
     @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SADATABASE', 
     @optname=N'rpc out', @optvalue=N'true'
GO
-- Set remote login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SADATABASE', 
     @locallogin = NULL , @useself = N'False', 
     @rmtuser = N'DBA', @rmtpassword = N'sql'
GO
-- Set global provider "allow in process" flag
EXEC master.dbo.sp_MSset_oledb_prop N'SAOLEDB.12', N'AllowInProcess', 1

Discuss this page in DocCommentXchange.