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:
- Remote server access.
- The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
- 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.
- Create a DSN to a Denodo database.
- Start the SQL Server Management Studio and under Object Browser expand Server Objects and right click Linked Servers as shown below:
- Click on New Linked Server. On the new window fill in the information as shown to create a linked server under the General tab.
- On the Security tab, select the ‘Be made using this security context’ option and enter the credentials to the Denodo Server.
- Finally, under the Server Options tab, change RPC and RPC Out options to True (by default they are set to False).
- 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
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 ‘DENODO8’ 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]
SELECT * FROM OPENQUERY(DENODO8,'SELECT * FROM bv_pg_products')
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:
SELECT * FROM [DENODO8].denodo_training.denodo_training.bv_pg_products
However, the option “Script Table as...SELECT To” does not work
How to access SQL Server Linked Server from Denodo?
To access Linked Server an ODBC data source from Virtual DataPort must be created using the SQL Server configuration:
- 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.
- 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.
- 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 OR REPLACE WRAPPER ODBC bv_odbc_readdata_linkedserver
FOLDER = '/02-baseview'
SQLSENTENCE='SELECT * FROM [DENODO8].[denodo_training].[denodo_training].bv_pg_products'
productid = 'productid' :'java.lang.Integer' (sourcetypedecimals='0', sourcetypesize='10', sourcetypeid='4', sourcetypename='int') SORTABLE NOT UPDATEABLE,
productname = 'productname' :'java.lang.String' (sourcetypedecimals='0', sourcetypesize='10', sourcetypeid='-9', sourcetypename='nvarchar') SORTABLE NOT UPDATEABLE,
productprice = 'productprice' :'java.lang.Integer' (sourcetypedecimals='0', sourcetypesize='10', sourcetypeid='4', sourcetypename='int') SORTABLE NOT UPDATEABLE
CREATE OR REPLACE TABLE bv_odbc_readdata_linkedserver I18N us_pst (
productid:int (sourcetypeid = '4', sourcetypedecimals = '0', sourcetypesize = '10'),
productname:text (sourcetypeid = '-9', sourcetypedecimals = '0', sourcetypesize = '10'),
productprice:int (sourcetypeid = '4', sourcetypedecimals = '0', sourcetypesize = '10')
FOLDER = '/02-baseview'
ADD SEARCHMETHOD bv_odbc_readdata_linkedserver(
ADD productid NOS ZERO ()
ADD productname NOS ZERO ()
ADD productprice NOS ZERO ()
OUTPUTLIST (productid, productname, productprice
WRAPPER (odbc bv_odbc_readdata_linkedserver)
Create the base view using the “Create Base View from query” option using as SQL query:
SELECT * FROM [DENODO8].[denodo_training].[denodo_training].bv_pg_products;
where DENODO8 is the name of the Linked Server.