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=publicPlease specify the user name and password if the public user is not used to access the data source.
DSN=MY_DSN;UID=mike;PWD=secretFor 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_dsnThe quoted '' becomes ', resulting in the SQL statement:
update customers set name = 'Schmidt' where custno = '1234'