Applies to:
Denodo 8.0
,
Denodo 7.0
,
Denodo 6.0
Last modified on: 19 May 2020
Tags:
JDBC data sources
SQL Server
Goal
This document shows how to execute SQL Server functions and stored procedures creating base views for them.
Content
Virtual DataPort allows connecting to databases using JDBC data sources. Once the connection to the database is set base views can be created in VDP. These base views can be created in two ways:
When using the first option, VDP displays an introspection tree for the data source that shows the schemas contained in the data source with its schemas and the tables, functions and stored procedures defined for each one.
The ability to create base views by importing functions or stored procedures directly for SQL Server was added to the Denodo Platform from version 5.5 update 20150629. In the previous version of the Denodo Platform the second option of using a SQL query must be used.
When using the second option, the SQL query will be sent to the database without modifications other than interpolation variables. These interpolation variables use the syntax @VARIABLE or @{VARIABLE}.
With this option, any SQL query can be performed in the data source, including the execution of SQL functions and stored procedures. The syntax that can be used to create a base view can depend on the underlying database server. For instance, to execute a function or stored procedure from Microsoft SQL Server the following syntax must be used:
where:
Known limitations for SQL Server stored procedures: