SQL Server 2022 Adapter Limitations

Supported Types
Common to All Operations
Read Table
Execute Select SQL
Write Table
Execute Update SQL
Execute Stored Procedure
Write by Loader
Major Exceptions

Supported Types

The types supported in this adapter are the following.

Type name	Support	Remarks
BIGINT	Yes
INT	Yes
SMALLINT	Yes
TINYINT	Yes
BIT	Yes
NUMERIC	Yes
DECIMAL	Yes
MONEY	Yes
SMALLMONEY	Yes
FLOAT	Yes
REAL	Yes
TIME	Limited
DATE	Yes
SMALLDATETIME	Yes
DATETIME	Limited
DATETIME2	Limited
DATETIMEOFFSET	Limited	Data is handled as String.
CHAR	Yes
VARCHAR	Yes
NCHAR	Yes
NVARCHAR	Yes
TEXT	Yes
NTEXT	Yes
BINARY	Limited
VARBINARY	Limited
IMAGE	Yes
VARCHAR(MAX)	Yes
NVARCHAR(MAX)	Yes
VARBINARY(MAX)	Limited
XML	Limited
SQL_VARIANT	No
UNIQUEIDENTIFIER	Yes

Yes: Supported.
Limited: Supported with limitations.
No: Not supported.

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

Common to All Operations

Specification Limits

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 property settings

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”.

Read Table

Specification Limits

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.

Execute Select SQL

Specification Limits

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.

Write Table

Specification Limits

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.

Execute Update SQL

Specification Limits

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.

Execute Stored Procedure

Specification Limits

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.
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.

Write by Loader

Specification Limits

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.

Notes

For how to describe format file, refer to “Non-XML Format Files”(https://docs.microsoft.com/en-us/sql/relational-databases/import-export/non-xml-format-files-sql-server?view=sql-server-ver16) and “XML Format Files”(https://docs.microsoft.com/en-us/sql/relational-databases/import-export/xml-format-files-sql-server?view=sql-server-ver16).

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.

Major Exceptions

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 2022 belong to.	Check the domain DataSpiderServer belong to and the one SQL Server 2022 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 ‘<invalid string in 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-12.4.<version>.<x64 or x86>.dll was placed.
	"encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.	The certificate is not imported to the keystore referred by DataSpiderServer.	Import a certificate to the keystore referred by DataSpiderServer. For more details on how to import a certificate, refer to “Import Certificate”.