Skip to content

sqlserver_user_guide.md

Latest commit

 

History

History
144 lines (111 loc) · 7.14 KB

sqlserver_user_guide.md

File metadata and controls

144 lines (111 loc) · 7.14 KB
Raw

Microsoft SQL Server SQL Dialect User Guide

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft.

Telemetry

This virtual schema uses telemetry-java to send anonymous feature-usage events.

For details on what is collected and how to disable telemetry, see the documentation.

Uploading the JDBC Driver to Exasol BucketFS

  1. Download the SQL Server JDBC driver. We recommend using a jre8 driver.

  2. Upload the driver to BucketFS, see the BucketFS documentation for details.

    Hint: Put the driver into folder default/drivers/jdbc/ to register it for ExaLoader, too.

Registering the JDBC driver for ExaLoader

In order to enable the ExaLoader to fetch data from the external database you must register the driver for ExaLoader as described in the Installation procedure for JDBC drivers.

  1. ExaLoader expects the driver in BucketFS folder default/drivers/jdbc.

    If you uploaded the driver for UDF to a different folder, then you need to upload the driver again.

  2. Additionally you need to create file settings.cfg and upload it to the same folder in BucketFS:

DRIVERNAME=SQLSERVER
JAR=mssql-jdbc-<version>.jre8.jar
DRIVERMAIN=com.microsoft.sqlserver.jdbc.SQLServerDriver
PREFIX=jdbc:sqlserver:
NOSECURITY=YES
FETCHSIZE=100000
INSERTSIZE=-1

Installing the Adapter Script

Upload the latest available release of SQL Server Virtual Schema to Bucket FS.

Then create a schema to hold the adapter script.

CREATE SCHEMA SCHEMA_FOR_VS_SCRIPT;

The SQL statement below creates the adapter script, defines the Java class that serves as entry point and tells the UDF framework where to find the libraries (JAR files) for Virtual Schema and database driver.

CREATE OR REPLACE JAVA ADAPTER SCRIPT SCHEMA_FOR_VS_SCRIPT.ADAPTER_SCRIPT_SQLSERVER AS
  %scriptclass com.exasol.adapter.RequestDispatcher;
  %jar /buckets/<BFS service>/<bucket>/virtual-schema-dist-14.0.4-sqlserver-3.0.1.jar;
  %jar /buckets/<BFS service>/<bucket>/mssql-jdbc-<version>.jre8.jar;
/

Defining a Named Connection

Define the connection to SQL Server as shown below. We recommend using TLS to secure the connection.

CREATE OR REPLACE CONNECTION SQLSERVER_JDBC_CONNECTION
TO 'jdbc:sqlserver://<server name>:<port>'
USER '<user>'
IDENTIFIED BY '<passsword>';

Creating a Virtual Schema

Below you see how an SQL Server Virtual Schema is created.

CREATE VIRTUAL SCHEMA <virtual schema name>
    USING SCHEMA_FOR_VS_SCRIPT.ADAPTER_SCRIPT_SQLSERVER
    WITH
    CONNECTION_NAME = 'SQLSERVER_JDBC_CONNECTION'
    CATALOG_NAME   = '<database name>'
    SCHEMA_NAME = '<schema name>';

Please, do not forget to specify the SCHEMA_NAME property.

Provide the SQL server's database name using one of the suggested ways:

  1. Via the CATALOG_NAME property;
  2. Via connection string definition: jdbc:sqlserver://<server name>:<port>/<database name>;

Data Types Conversion

MS SERVER Data Type Supported Converted Exasol Data Type Known limitations
BIGINT ✓ DECIMAL
BINARY ×
BIT ✓ BOOLEAN
CHAR ✓ CHAR
DATE ✓ DATE
DATETIME ✓ TIMESTAMP
DATETIME2 ✓ TIMESTAMP
DATETIMEOFFSET ✓ VARCHAR(34)
DECIMAL ✓ DECIMAL
FLOAT ✓ DOUBLE PRECISION
GEOMETRY ×
GEOGRAPHY ×
HIERARCHYID ×
IMAGE ×
INT ✓ DECIMAL
MONEY ✓ DECIMAL
NCHAR ✓ CHAR
NTEXT ✓ VARCHAR(2000000)
NVARCHAR ✓ VARCHAR
NUMERIC ✓ DECIMAL
SQL_VARIANT ×
REAL ✓ DOUBLE PRECISION
ROWVERSION ×
SMALLDATETIME ✓ TIMESTAMP
SMALLINT ✓ DECIMAL
SMALLMONEY ✓ DECIMAL
TEXT ✓ VARCHAR(2000000)
TIME ✓ VARCHAR(16)
TINYINT ✓ DECIMAL
UNIQUEIDENTIFIER ✓ CHAR(36)
VARBINARY ×
VARCHAR ✓ VARCHAR
XML ×

Testing information

In the following matrix you find combinations of JDBC driver and dialect version that we tested.

Virtual Schema Version SQL SERVER Version Driver Name Driver Version
2.1.1 2019-CU17-ubuntu-20.04 MS SQL JDBC JRE 8 11.2.0.jre8
2.1.2 2022-CU10-ubuntu-22.04 MS SQL JDBC JRE 8 12.4.2.jre8
2.1.4 2022-CU17-ubuntu-22.04 MS SQL JDBC JRE 8 12.8.1.jre8
3.0.0 (Latest) 2025-CU4-ubuntu-24.04 MS SQL JDBC JRE 8 13.4.0.jre8

Known Issues

  • Select with boolean expressions, such as SELECT c1 = 1 or SELECT c2 IS NULL won't work with this Virtual Schema because SQL Server doesn't support a boolean data type. But the same behaviour can be achieved using a CASE WHEN expression, i.e. SELECT CASE WHEN c1 = 1 THEN true ELSE false END or SELECT CASE WHEN c2 IS NULL THEN true ELSE false END as in this case the Virtual Schema will handle the result as an SQLServer BIT type that will be converted to an Exasol BOOLEAN.

  • SQL Server doesn't support NULLS FIRST / LAST syntax and always orders NULL first when using ascending order, and last when using descending order. As a workaround an NVL function can be used with the highest value depending on the column type, e.g. ORDER BY NVL(NUM, 9999999999), NVL(NAME, 'ZZZZZZ')