Setting up a Microsoft Linked Server using OLE DB

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 4-part table referencing syntax or the Microsoft 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 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 SQL Anywhere server for execution.

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.

 To set up a Linked Server interactively

  1. For Microsoft SQL Server 2005, start Microsoft 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, drill down to Server Objects » Linked Servers in the tree view. Right-click in the Linked Servers area and select New Linked Server.

  2. 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 chosen, and SQL Anywhere OLE DB Provider 12 should be chosen from the list. The Product Name field can be anything you like (for example, SQL Anywhere). The Data Source field should contain an ODBC data source name (for example, SQL Anywhere 12 Demo). The Provider String field can contain additional connection parameters such as DATABASEFILE (DBF). Other fields, such as Location, on the General page should be left empty.

  3. Instead of specifying the database 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, choose the Be made using this security context option and fill in the Remote login and With password fields (the password is displayed as asterisks).

  4. Choose 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. 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 set if you want to execute stored procedure/function calls in a SQL Anywhere database and pass parameters in and out successfully.

  5. 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, right-click the SAOLEDB.12 provider in the Linked Servers » Providers tree view and choose Properties. Make sure the Allow Inprocess checkbox is selected. If the Inprocess option is not selected, queries fail.

 To set up a Linked Server using a script

  • A Linked Server may be set up using a SQL Server script similar to the following. Make the appropriate changes to the script before running it under SQL Server. You should select a new Linked Server name (SADATABASE is used in the example), a data source name (SQL Anywhere 12 Demo is used in the example), and a remote user ID and password. 



    USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server=N'SADATABASE', 
     @srvproduct=N'SQL Anywhere', @provider=N'SAOLEDB.12', 
     @datasrc=N'SQL Anywhere 12 Demo'
GO
EXEC master.dbo.sp_serveroption @server=N'SADATABASE', 
     @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SADATABASE', 
     @optname=N'rpc out', @optvalue=N'true'
GO
-- Set remote login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SADATABASE', 
     @locallogin = NULL , @useself = N'False', 
     @rmtuser = N'DBA', @rmtpassword = N'sql'
GO
-- Set global provider "allow in process" flag
EXEC master.dbo.sp_MSset_oledb_prop N'SAOLEDB.12', N'AllowInProcess', 1
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0