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 16 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 16 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 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.16 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.

Other provider options can be ignored. Several of these options pertain to SQL Server backwards compatibility and have no effect on the way SQL Server interacts with the SQL Anywhere OLE DB provider. Examples are Nested queries and Supports LIKE operator. Other options, when selected, may result in syntax errors or degraded performance.