SQL Server 2012 JDBC Adapter Limitations

Supported Types

This adapter supports types as follows.

See SQL Server 2012 documentation for any details regarding data types.

Databases with names containing the following characters cannot be specified. Databases with names containing characters other than the following still may cannot be specified.
Contact the database vendor for more information.

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

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

Connection properties are set with db_sqlserver.properties file at $DATASPIDER_HOME\server\plugin\data_processing\share\lib\classes.

Property Name	Description	Remarks
sendStringParametersAsUnicode	[true]: Indicates that the data is sent as Unicode. [false]: Sent to server by the default collation of the database. When referring to index of string data other than Unicode, converting overhead from Unicode does not occur, thus the performance is improved.	The value defaults to true.

Table List View
- Table with schema other than dbo schema is shown as <schema name>.<table name>.
TIME
- Up to the millisecond values are retrieved.
DATETIME2
- Up to the millisecond values are retrieved.
Upper/lower case is not distinguished in table name even when table name is enclosed in quotation marks.

Table List View
- Table with schema other than dbo schema is shown as <schema name>.<table name>.
TIME
- Up to the millisecond values are retrieved.
DATETIME2
- Up to the millisecond values are retrieved.
Upper/lower case is not distinguished in table name even when table name is enclosed in quotation marks.

TIME
- When SQL Parameter is used, time with milliseconds can be specified.
DATETIME2
- When SQL Parameter is used, time with milliseconds can be specified.

TIME
- Input parameter takes a time with milliseconds
- Output parameter can reference a time with milliseconds
DATETIME2
- Input parameter takes a time with milliseconds
- Output parameter can reference a time with milliseconds
XML
- Cannot execute when the arguments of the procedure contains XML type.
SQL_VARIANT
- Cannot execute when the arguments of the procedure contains SQL_VARIANT type.
UNIQUEIDENTIFIER
- Cannot execute when the arguments of the procedure contains UNIQUEIDENTIFIER type.
May not be able to use procedures that requires being enclosed in quotation marks.

If you specify a procedure that uses output parameter, you will need input data since the same parameter as the output parameter will appear in the input parameter setting.

Table List View
- Table with schema other than dbo schema is shown as <schema name>.<table name>.
BINARY, VARBINARY, VARBINARY (MAX)
- When data file contains control codes, the format file must be specified.
Upper/lower case is not distinguished in table name even when table name is enclosed in quotation marks.

If [Delimiter] is specified in double-byte characters, error occurs during execution due to the specifications of SQL Server.

Please refer to Books Online for SQL Server 2012 on "Non-XML format file" and "XML Format File".

Notes on the specified file
- If you have no privilege to refer to file specified in [Data file] or [Format file], the following error message will be output to XML log.
  "File "<file path>" could not open, bulk read failed. Operating system error 5 (Access denied. )"
  
  Please change the account executing service of SQL Server instance to an account with privilege of reference.

Exception name		Cause	Actions
com.microsoft.sqlserver.jdbc.SQLServerException	user '<username>' Login failed.	Connected to the database but cannot login.	Check [User name]/[Password].
	You could not login. Since this login is from the untrusted domain, you cannot use in the Windows authentication.	The domain DataSpiderServer belong and the domain SQL Server 2012 belong are NOT in the trust relationship.	Please confirm each domain related to DataSpiderServer and SQL Server 2012.
	Database requested in login "<database>" cannot be opened. Login failed.	The specified database name may be incorrect.	Ensure the specified [Database name] is correct.
	Connection to host <host>, port <port number> has failed. Error: timed out. Make sure the connectivity with the SQL server is not compromised.	Cannot connect to the database.	Check the network connectivity and ensure the [Hostname] specified is valid.
	object name '<table name>' is invalid.	"<table name>" does not exist.	Make sure the specified table exists.
	Incorrect syntax near '<table name>'.	The table name may not follow the rules for standard identifiers of Transact-SQL.	In that case, enclose the specified table name with quotation marks.
	This driver is not configured to be able to perform integrated authentication.	Occurs when fails to connect using Windows integrated authentication.	Please check the directory where sqljdbc_auth.dll is deployed.