SQL/R A.03.00 Linked Server

SQL/R may be used to define a linked server for the MS SQL server. This makes the Eloquence database accessible through the MS SQL server.

Definiting a Linked Server

In the object explorer of the SQL Server Management Studio please open the SQL server and right-click on "Server Objects" -> "Linked Server". Please select "New Linked Server ...".

The following information must be provided:

• Linked Server - please choose a name (this name is used subsequently in the SQL statements)
• Server Type - "Other data source"
• Provider - "Microsoft OLE DB Provider for ODBC Drivers"
• Product Name - SQLR
• Data source - (leave empty)
• Provider string - Connection-String for your data source, please see below
• Location - (leave empty)
• Catalog - (leave empty)

A valid Connection String for the SQL/R data source must be provided for "Provider String".
For example, asuming the ODBC data source "MY_DSN" is configured to allow access to the Eloquence database this would result in the follwing entry:

DSN=MY_DSN;UID=public

Please specify the user name and password if the public user is not used to access the data source.

DSN=MY_DSN;UID=mike;PWD=secret

For more information on the Connection String please refer to the Getting started with SQL/R documentation, section "Using an ODBC connection string".

If this was configured correctly, the SQLR data source (and the Eloquence database) is now acessible by the SQL Server.

Accessing a Linked Server

The openquery method may be used to access the linked server. For example,

select * from openquery(my_dsn, 'select * from customers')

Where "my_dsn" is the name of the linked servers that was specified when defining the linked server.

Unfortunately, the openquery method is limited as it does not allow using variables in the openquery statement. For more information on the openquery method please refer to:
http://msdn.microsoft.com/en-us/library/ms188427.aspx

The alternative is to use the EXEC (...) AT linked_server syntax. The (somewhat complex) documentation is available at:
http://msdn.microsoft.com/en-us/library/ms188332.aspx

Example for the UPDATE statement using variables:

Declare @Cusno varchar(10)
Declare @Name varchar(30)

Set @Cusno = '1234'
Set @Name = 'Schmidt'

exec ('update customers set name = ''' + @Name + '''
       where custno = ''' + @Cusno + ''') at my_dsn

The quoted '' becomes ', resulting in the SQL statement:

update customers set name = 'Schmidt' where custno = '1234'