“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:
A linked server definition specifies the following objects:
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.
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:
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
To access a Linked Server an ODBC data source from Virtual DataPort must be created using the SQL Server configuration:
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.
Create a new ODBC data source choose the DSN previously created and give a name to the data source.
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
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 (
ALTER TABLE linkedserver_view
ADD SEARCHMETHOD linkedserver_view(
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.