Microsoft Linked Servers

A Microsoft Linked Server can be created that uses the SAP Sybase IQ OLE DB provider to obtain access to a SAP Sybase IQ 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 SAP Sybase IQ database, and Customers is the table name in the SAP Sybase IQ 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 SAP Sybase IQ server for execution.

For complex queries, OPENQUERY may be the better choice since the entire query is evaluated on the SAP Sybase IQ server. With the four-part syntax, SQL Server may retrieve the contents of all tables referenced by the query before it can evaluate it (for example, queries with WHERE, JOIN, nested queries, etc.). For queries involving very large tables, processing time may be very poor when using four-part syntax. In the following four-part query example, SQL Server passes a simple SELECT on the entire table (no WHERE clause) to the SAP Sybase IQ database server via the OLE DB provider and then evaluates the WHERE condition itself.

SELECT ID, Surname, GivenName FROM [SADATABASE].[demo].[GROUPO].[Customers]
WHERE Surname = 'Elkins'

Instead of returning one row in the result set to SQL Server, all rows are returned and then this result set is reduced to one row by SQL Server. The following example produces an identical result but only one row is returned to SQL Server.

SELECT * FROM OPENQUERY( SADATABASE, 
    'SELECT ID, Surname, GivenName FROM [GROUPO].[Customers]
    WHERE Surname = ''Elkins''' )

You can set up a Linked Server that uses the SAP Sybase IQ 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 SAP Sybase IQ 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.