SQL Server 2005 JDBC 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

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

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	-	%	&	(	)	=	[	]	{	}	'	^	;

Connection property settings

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

Read Table

Specification Limits

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

Execute Select SQL

Specification Limits

Write Table

Specification Limits

• Displaying table list
  • Tables that exist in schemas other than dbo will be displayed as "<schema name>.<table name>".
• CHAR
  • String of which size exceeds 4000 bytes cannot be written.
• The table name is case-insensitive even if it is enclosed with quotation marks.

Execute Update SQL

Specification Limits

• CHAR
  • String of which size exceeds 4000 bytes cannot be written.

Execute Stored Procedure

Specification Limits

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


• Cannot get the return value of RETURN clause.


• 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>.
• CHAR
  • String of which size exceeds 4000 bytes cannot be written.
• 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” and “XML Format Files” in SQL Server 2005 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.

Major Exceptions