SQL Server 2019 JDBC Adapter Limitations

Supported Types

The types supported in this adapter are the following.

For more details on the specifications of database, such as rounding off and value range of each type, refer to SQL Server 2019 documents.

Cannot connect to a database whose name includes the following characters. There are some other characters which disables connection to the database.
For more details, inquire the database vendor.

Characters - % & ( ) = [ ] { } ' ^ ;

Characters	-	%	&	(	)	=	[	]	{	}	'	^	;

Connection properties can be configured in the file db_sqlserver.properties under $DATASPIDER_HOME\server\plugin\data_processing\share\classes.

Property name	Description	Remarks
sendStringParametersAsUnicode	[true]: Data will be sent as Unicode. [false]: Data will be sent to the server in the default collation. When referring to an index of non-Unicode character data, the performance is enhanced as conversion overhead from Unicode do not occur.	Default value is “true”.

Displaying table list
- Tables that exist in schemas other than dbo will be displayed as "<schema name>.<table name>".
TIME
- Can be retrieved up to milliseconds.
DATETIME2
- Can be retrieved up to milliseconds.
The table name is case-insensitive even if it is enclosed with quotation marks.

TIME
- Can be retrieved up to milliseconds.
DATETIME
- If you are substituting the value of DATETIME type available up to milliseconds using SQL parameter for WHERE clause, it cannot be accurately determined by the comparison operator. However, this can be avoided by converting the value into string format "yyyy-MM-dd HH:mm:ss.SSS" with Mapper and changing the type of SQL parameter to VARCHAR type.
DATETIME2
- Can be retrieved up to milliseconds.

Displaying table list
- Tables that exist in schemas other than dbo will be displayed as "<schema name>.<table name>".
TIME
- Can be fetched up to milliseconds.
DATETIME2
- Can be fetched up to milliseconds.
The table name is case-insensitive even if it is enclosed with quotation marks.

TIME
- When using SQL parameter, it can be specified up to milliseconds.
DATETIME
- If you are substituting the value of DATETIME type available up to milliseconds using SQL parameter for WHERE clause, it cannot be accurately determined by the comparison operator. However, this can be avoided by converting the value into string format "yyyy-MM-dd HH:mm:ss.SSS" with Mapper and changing the type of SQL parameter to VARCHAR type.
DATETIME2
- When using SQL Parameter, it can be specified up to milliseconds.

TIME
- Input parameters can be specified up to milliseconds.
- Output parameters can be retrieved up to milliseconds.
DATETIME2
- Input parameters can be specified up to milliseconds.
- Output parameters can be retrieved up to milliseconds.
XML
- Cannot execute when XML type is included in procedure arguments.
SQL_VARIANT
- Cannot execute when SQL_VARIANT type is included in procedure arguments.
UNIQUEIDENTIFIER
- Cannot execute when UNIQUEIDENTIFIER type is included in procedure arguments.
There are cases that a procedure with a name that has to be enclosed with quotation marks cannot be used.

When procedure to use output parameters is specified, the same as the output parameters will be displayed in the input parameter settings, and input data will be required.

Displaying table list
- Tables that exist in schemas other than dbo will be displayed as <schema name>.<table name>.
BINARY, VARBINARY, and VARBINARY(MAX)
- When a data file includes control codes, you need to specify a format file.
The table name is case-insensitive even if it is enclosed with quotation marks.

When specifying full-width character in [Delimiter], an error occurs as the SQL Server specification.

For how to describe format file, refer to “Non-XML Format Files” and “XML Format Files” in SQL Server 2019 Books Online.

File to specify
- When you have no read permission to [Data file] or [Format file], the following error message will be output to XML log at runtime.
  “Cannot bulk load because the file "<file path>" could not be opened. Operating system error code 5(Access is denied.)”
  
  Change the account executing service of SQL Server instance to an account with read permission.

Exception name		Cause	Solution
com.microsoft.sqlserver.jdbc.SQLServerException	Login failed for user '<user name>'.	The user name or the password is incorrect.	Check [User name] or [Password] of the global resource.
	Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.	There is no trust relationship between the domain DataSpiderServer belong to and the one SQL Server 2019 belong to.	Check the domain DataSpiderServer belong to and the one SQL Server 2019 belong to.
	Cannot open database "<database name>" requested by the login. The login failed.	The database is invalid.	Check [Database name] of the global resource.
	The TCP/IP connection to the host <host name>, port <port number> has failed.	Unable to connect to the database.	Check [Host name] of the global resource, or the network.
	Invalid object name '<table name>'.	The table does not exist.	Specify an existing table in [Table name].
	Incorrect syntax near ‘<table name>’.	The table name may not be in accordance with the rules of Transact-SQL standard identifier.	Enclose the table specified in [Table name] with quotation marks.
	This driver is not configured for integrated authentication.	Library for Windows authentication may not be placed.	Check the directory where mssql-jdbc_auth-8.2.0.x64.dll was placed.