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 Microsoft's 4-part table referencing syntax or Microsoft's OPENQUERY SQL function. An example of the 4-part syntax follows.
SELECT * FROM SADATABASE..GROUPO.Customers |
In this example, SADATABASE is the name of the Linked Server, GROUPO is the table owner in the SQL Anywhere database, and Customers is the table name in the SQL Anywhere database. The catalog name is omitted (as indicated by two consecutive dots) since catalog names are not a feature of SQL Anywhere databases.
The other form uses Microsoft's 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.
To set up a Linked Server that uses the SQL Anywhere OLE DB provider, a few steps must be followed.
To set up a Linked Server
Fill in the General page.
The Linked Server field on the General page should contain a Linked Server name (like SADATABASE used above). The Other Data Source option should be selected, and SQL Anywhere OLE DB Provider should be selected from the list. The Product Name field should contain an ODBC data source name (for example, SQL Anywhere 11 Demo). The Provider String field can contain additional connection parameters such as user ID and password (for example, uid=DBA;pwd=sql). Other fields, such as Data Source, on the General page should be left empty.
Select 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 select this option. In SQL Server 2005, there is a global Allow Inprocess checkbox when you right-click the SAOLEDB provider in the Linked Servers/Providers tree view and choose Properties. If the InProcess option is not selected, queries fail.
Select 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 selected for these two options. These check boxes are found on the Server Options page. In SQL Server 2005, the options are True/False settings. Make sure that they are set True. The Remote Procedure Call (RPC) options must be selected if you want to execute stored procedure/function calls in a SQL Anywhere database and pass parameters in and out successfully.
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |