[SOL-178] IMPORT/EXPORT with Microsoft SQL Server Created: 11.09.2014  Updated: 22.06.2020  Resolved: 22.06.2020

Status: Obsolete
Project: Solution Center
Component/s: EXAloader
Affects Version/s: EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.x
Fix Version/s: None

Type: How To
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: Export, Import, JDBC, SQLServer

Attachments: PNG File EXAoperation_Add_JDBC_Driver.PNG     PNG File EXAoperation_Overview_JDBC_Drivers.PNG    
Issue Links:
Related
is related to SOL-108 Best Practices for MS SQL Server IMPO... Obsolete
Solution:

Note: This solution is no longer maintained. For the latest information, please visit our Knowledge Base:

https://community.exasol.com/t5/connect-with-exasol/best-practices-for-import-export-with-sql-server/ta-p/215

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Background

Which JDBC driver for SQL Server shall I use?

Exasol is shipped with the open source JDBC driver JTDS.

Prerequisites

Have a look at EXAoperation to see all predefined JDBC drivers:

 

It's also possible to configure and upload the original Microsoft JDBC driver:

 

Please use the following parameters:

  • Source: Path to the MSSQL driver (sqljdbc4.jar)
  • Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • Prefix: jdbc:sqlserver:
  • Name: MSSQL

We recommend the JTDS driver for best performance, but:
If you do single loads with more than 4,294,967,295 rows, you should use the MSSQL native JDBC driver due to a bug in JTDS (note that the number 4,294,967,295 is the maximum value for a 32-bit unsigned integer). If you used JTDS for such big loads, only 4,294,967,295 rows would be imported, and the rest would be ignored without seeing an error.
See also SOL-108 if you want to use the MSSQL native JDBC driver.

How to import with Microsoft SQL Server

Step 1. CREATE CONNECTION

In EXAplus execute the following query to create a connection object in EXASolution:

CREATE CONNECTION sql_server_jdbc_con_jtds
TO 'jdbc:jtds:sqlserver://10.78.0.178:1433/EXATEST'
USER 'exatest'
IDENTIFIED BY 'test';

It is also possible to use Windows Authentication in conjunction with this driver. In order to enable this authentication, simply add the parameters "useNTLMv2=true" and "domain=[Domain name]", like so:

CREATE CONNECTION sql_server_jdbc_con_jtds
TO 'jdbc:jtds:sqlserver://10.78.0.178:1433;DatabaseName=EXATEST;domain=AD;useNTLMv2=true;'
USER 'username' -- Windows Username
IDENTIFIED BY 'AD password here' --Windows password;

Once the AD user/password are defined in the database connection (USER '' IDENTIFIED BY ''), they can be re-used as often as needed (as long as the credentials are valid). Please note that the passwords are masked in all SQL texts and logs. With this method, you can grant the connection only to the required users on EXASOL side and it can be used to IMPORT data from SQL server. 

Step 2. Write IMPORT/EXPORT Statement

Test the connectivity by querying the SQL Server system catalog, e.g.:

select * from 
(
import from jdbc at sql_server_jdbc_con_jtds
statement 'select * from information_schema.tables'

);

If the query returns a resultset, you are ready to IMPORT some tables.
Otherwise please check your network settings and/or the settings of your SQL Server.

NOTE: As of Version 6.1, you will need the system privilege IMPORT to perform the statement.

Step 3. Import data from SQL Server

Now you can IMPORT/EXPORT data from the SQL Server, e.g.:

import into MYTABLE  from jdbc at sql_server_jdbc_con_jtds
statement 'select * from [dbo].[Table_1]';

export MYTABLE into jdbc at sql_server_jdbc_con_jtds table [dbo].[Table_1];

Category 1: ETL / Data Integration
Category 2: SQL
Generated at Tue Dec 07 19:58:05 CET 2021 using Jira 7.13.18#713018-sha1:e1230154f8ff8cc9272975bf568fc732e806fd68.