Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Programming » OLE DB and ADO development


Microsoft Linked Servers

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.


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.


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

For complex queries, OPENQUERY may be the better choice since the entire query is evaluated on the SQL Anywhere 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 SQL Anywhere 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 ID, Surname, GivenName FROM [GROUPO].[Customers]
    WHERE Surname = ''Elkins''' )

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.


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.

Setting up a Linked Server using an interactive application
Setting up a Linked Server using a script