Accesing Microsoft SQL Server Linked Servers

Applies to: Denodo 7.0 , Denodo 6.0 , Denodo 5.5 , Denodo 5.0
Last modified on: 27 Mar 2018
Tags: Connectivity ODBC data sources ODBC driver SQL Server

Download document

What are Microsoft SQL Server Linked Servers?

“Linked Server” is a feature of MS SQL Server that allows access to non-SQL Server databases through SQL Server. Only SQL Server Developer and SQL Server Enterprise versions support Linked Servers. SQL Server Express edition has several limitations that do not allow configuring or using Linked Servers.

A Linked Server enables the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Many types of OLE DB data sources can be configured as linked servers such as  Microsoft Access, Excel, another instance of SQL Server, etc.

Linked servers offer the following advantages:

  1. Remote server access.
  2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  3. The ability to address diverse data sources similarly.

A linked server definition specifies the following objects:

  • An OLE DB provider: DLL that manages and interacts with a specific data source
  • An OLE DB data source: Identifies the specific database that can be accessed through OLE DB (although data sources queried through linked server definitions are ordinarily databases, OLE DB providers exist for a variety of files and file formats.)

How to configure a SQL Server Linked Server to connect to Denodo?

Virtual DataPort provides access to ODBC clients through the PostgreSQL ODBC driver. To install the driver, follow the steps of the Virtual DataPort Developer Guide Section “Access through ODBC”.

Once the DSN to a Denodo database  is created on the system, a Linked Server to Denodo can be configured.

        

  1. Create a DSN to a Denodo database.

odbc_admin.png

dsn.png

  1. Start the SQL Server Management Studio and under Object Browser expand Server Objects and right click Linked Servers as shown below:

  1. Click on New Linked Server. On the new window fill in the information as shown to create linked server under the General tab.

new_linked_server.png

  1. On the Security tab, select the  ‘Be made using this security context’ option and enter the credentials to the Denodo Server.

security_tab.png

  1. Finally, under the Server Options tab, change RPC and RPC Out options to True (by default they are set to False).

server_options.png

  1. Click Ok, after making all the mentioned changes. This will create the linked server and it will be listed under SSMS Object Browser. Right Click on the new linked server and click on Test Connection

test_connection.png

Accessing and Querying Denodo through SSMS

IMPORTANT NOTE: For performance reasons, due to some limitations on the OLEDB driver, to query Virtual DataPort views, the use of the openquery function is recommended. When using this function any WHERE condition or operation included in the query will be delegated to Virtual DataPort. However, if a regular SELECT statement using the full path for the view within the Linked Server is issued, any condition or operation in the query will not be sent to Virtual DataPort and the query will return all the records coming from the Virtual DataPort view that will have to be post-filtered.

Once the Linked server is configured, open a query tab from SQL Server Management Studio and run a select query like the following, where ‘DENODO’ is the given name for the Linked Server:

openquery.png

Note that to run the query we have to use the openquery function that takes two input parameters: first, the name of the linked server, second, the query to run.

When we use this type of queries, and Lazy Schema Validation is not set to True, SQL Server will issue two queries against Denodo, the first one will be a metadata discovery query (only the first time we issue the query).

To create a view in a SQL Server schema that points directly to a linked table, this sentence can be executed in SQL Server:

create view [dbo].[client] as select * from openquery(DENODO,'select * from client')

In this case, a ‘client’ view will be created on the local SQL Server schema.

Even without creating a local view it is possible to access the linked table through the Linked Server full path, for instance:  DENODO.data_virtualization.data_virtualization.client

However, the option “Script Table as...SELECT To” does not work

select_to.png

select_to_error.png

How to access to a SQL Server Linked Server from Denodo?

To access a Linked Server an ODBC data source from Virtual DataPort must be created using the SQL Server configuration:

  1. Create a DSN

Create a DSN on the system that points to the database to access. Choose SQL Native Client driver and do not select any database to be able to access all the configured Linked Servers.

  1. Create a new ODBC data source

Create a new ODBC data source choose the DSN previously created and give a name to the data source.

  1. Create a base view

Introspection will not be available for the configured Linked Servers. Introspection will work for the elements in the SQL Server schemas.

 

An option to use the introspection is to create views on the local schemas on top of the linked tables as we saw in the previous section. Other options are:

Create the base views to Linked Servers using VQL, for instance:

  CREATE WRAPPER ODBC linkedserver_test

  DATASOURCENAME=linkedserver  

  RELATIONNAME='DENODO.data_virtualization.data_virtualization.client'        

  OUTPUTSCHEMA (

  client_id = 'client_id' :'java.lang.String' (OPT) NOT NULL SORTABLE,

  name= 'name' :'java.lang.String' (OPT) NOT NULL SORTABLE,

  surname = 'surname' :'java.lang.String' (OPT) NOT NULL SORTABLE,

  client_type = 'client_type' :'java.lang.String' (OPT) NOT NULL SORTABLE

  );

  DROP VIEW IF EXISTS linkedserver_view CASCADE;

  CREATE TABLE linkedserver_view I18N us_pst (

  client_id:text,

  name:text,

  surname:text,

  client_type:text

  );

   ALTER TABLE linkedserver_view

   CACHE OFF

   TIMETOLIVEINCACHE 0

   ADD SEARCHMETHOD linkedserver_view(

          I18N us_pst

          CONSTRAINTS (

               ADD client_id (any) OPT ANY

               ADD name (any) OPT ANY

               ADD surname (any) OPT ANY

               ADD client_type (any) OPT ANY

          )

          OUTPUTLIST (client_id, name, surname, client_type

          )

          WRAPPER (odbc  linkedserver_test)

  );

 

Create the base view using the “Create Base View from query” option using as SQL query:

SELECT * FROM DENODO.data_virtualization.data_virtualization.client

where DENODO is the name of the Linked Server.

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training