Connect Denodo from SQL Server Database using Linked Server

SQL Server database administrator can connect Denodo data virtualization platform server using Linked Server via an ODBC connection defined as System DSN on the instance server. Since Denodo VDP is a data platform enabling data consumers to connect seamlessly to very different kinds of data sources at the same time without dealing the complexity at the data source physical access layer. Connecting Denodo from SQL Server will let SQL developers to bring data together from many different sources like relational databases, web services, etc with the help of Denodo data virtualization software.

In this tutorial, first I will provide SQL Server administrators the reference article to install Denodo ODBC driver and create a System DSN for Denodo connection on the SQL Server instance machine. Then most of this article will explain how to create Linked Server for Denodo connection on the SQL Server instance for database developers.

First of all, install Denodo ODBC driver and create System DSN for connection to Denodo server using ODBC.
Please review the tutorial Install Denodo ODBC Driver and Create ODBC Connection to Denodo Server for details.

Assume you have followed the tutorial mentioned above to create system DSN on ODBC data sources screen and successfully established an ODBC connection to the target Denodo VDP with following configuration.

Now SQL Server database administor is ready to create Linked Server for Denodo connection from the SQL Server instance where the ODBC driver and system DSN is created.

As initial step, launch SQL Server Management Studio and connect to the target SQL Server instance.

On SQL Server Management Studio (SSMS) within Object Explorer window, drill down to Linked Servers.
Right click on Linked Servers node and select option "New Linked Server..."

On "New Linked Server" dialog screen on "General" page, provide following information.
Type a descriptive name in Linked server textbox
Choose "Other data source" as Server type

It is important to choose Microsoft OLE DB Provider for ODBC Drivers from the Provider combobox.

You can type any text in Product name input area. I preferred to type "Denodo" for product name value.

It is important type the same text as you defined the ODBC connection for Denodo server into the Data source input textbox. If you check the first picture you can see I used DenodoVDP as the data source name, so I used the same value on Linked Server creation page for Data Source name.

Leave empty the Provider string, Location and Catalog textboxes.

After provider and ODBC data source name is entered on General page into corresponding fields, switch to "Security" page.
As seen in following screenshot, select option "Be made using this security context" and provide a valid user name and password into "Remote login" and "With password" fields.

One last configuration setting we should implement before completing the Linked Server creation on SQL Server to connect Denodo server is on Server Options page.
As seen in below screenshot, change the RPC and RPC Out option values from default "False" values to True

Now click OK to complete Linked Server definition creation.
If an error occurs then please check the provider type and be careful that the data source must match exactly with the ODBC DSN name.
If they are also OK, please validate the ODBC connection details on ODBC Data Source Administrator tool.

SQL Server database administrator or SQL developers can right click on the Linked Server name and select "Test Connection" option to see if the connection to Denodo server from SQL Server instance can be successfuly established.

Additionally, SQL developers can drill-through the Linked Server node that they have created for Denodo connection to see the Denodo database object list like tables and views.

Now we are ready to consume data from Denodo data source using SQL queries. Let's execute a sample SQL query on Denodo Linked Server connection.

SELECT * FROM OPENQUERY(KodyazDenodo, 'select * from bitutorial.bv_areas')

On above sample SQL code, please note that we preferred to use OpenQuery syntax.
SQL developers should pass the Linked Server name as the first argument just like I did in the sample code.
The second argument is the SQL query referencing the table or view objects directly as seen on the left side of the below screenshot. This is the underlying objects list when you drill-through the Linked Server node.
Please note that, while creating the Linked Server to Denodo, we defined a target database name which is datahub in my case. With SQL OpenQuery syntax we don't need to use this Denodo database name in SQL code. Database developers directly use the schema name and the table or view name in FROM clause of the SQL query.

I will suggest SQL programmers to use OpenQuery syntax because directly referencing to the table/view with following approach might fail because of Nullable control on data columns by ODBC API mismatch with data itself.

Let's run following SQL code to fetch the same data from Denodo Linked Server on SQL Server database

SELECT * FROM [KODYAZDENODO].[datahub].bitutorial.[bv_areas]

This time I got following SQL error

Msg 7356, Level 16, State 1, Line 10
The OLE DB provider "MSDASQL" for linked server "KODYAZDENODO" supplied inconsistent metadata for a column. The column "id" (compile-time ordinal 1) of object ""datahub"."bitutorial"."bv_areas"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

The SQL exception message DBCOLUMNFLAGS_ISNULLABLE indicates a mind confusing information about the id column. Does it accept NULL values or not. When I log on to Denodo Virtual DataPort Administation Tool, I see that the id column is part of a PK (primary key) and does not accept NULL values. In addition to that there is no NULL values in the target base view on Denodo.

Just for the sake of not dealing with such errors, I strongly suggest using SQL OpenQuery function for database developers to build their SQL applications on Denodo platform which is accessible via Linked Server connections on SQL Server instances.