Setting up a Linked Server Using an Interactive Application

Use a Microsoft SQL Server interactive application to create a Microsoft Linked Server that uses the SAP Sybase IQ OLE DB provider to obtain access to an SAP Sybase IQ database.

Prerequisites

SQL Server 2000 or later.

Task

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, SAP Sybase IQ 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: SAP Sybase IQ 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 SAP Sybase IQ 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 an SAP Sybase IQ 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 SAP Sybase IQ OLE DB provider. Examples are Nested queries and Supports LIKE operator. Other options, when selected, may result in syntax errors or degraded performance.

The Microsoft Linked Server is configured.