SQL Server data type mapping and issues

The behavior of the Actuate SQL DatePart(), DateAdd(), and DateDiff() functions sometimes differs from the behavior of the corresponding Transact-SQL functions.

If you want to use the Actuate collation UNICODE_BIN against a SQL Server database that uses a collation other than unicode_bin, you must install the SQL_Latin1_General_Cp850_BIN2 collation. This installation requires HotFix 816039 from Microsoft. For information about HotFix 816039, go to:

http://support.microsoft.com/?id=816039

Using parameters in a GROUP BY clause may result in a SQL Server database error. For example, a query of the form:

SELECT CUSTID+? FROM CUSTOMERS

GROUP BY CUSTID+?

results in the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'EIITESTDB.dbo.CUSTOMERS.CUSTID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

SQL Server considers the two parameters to be different since they are not named parameters. The solution is to include a reference to the CUSTID column in the GROUP BY clause.

Table 8-4 shows how Transact-SQL data types map to Actuate SQL data types.

Table 8-4

Mapping of Transact-SQL data types to Actuate SQL data types

Transact-SQL data type	Actuate SQL data type	Compiled to Transact-SQL data type	Transact-SQL data type limitations
CHAR	VARCHAR	NVARCHAR	Transact-SQL NVARCHAR has a maximum length of 4000 characters on SQL Server.
DATETIME	TIMESTAMP	DATETIME	Transact-SQL DATETIME stores values from January 1, 1753. The accuracy of dates is to one three-hundredths of a second (3.33 milliseconds).
DECIMAL	DECIMAL	DECIMAL	The maximum number of significant digits (precision) for Transact-SQL DECIMAL is 38.
FLOAT	DOUBLE	FLOAT
INT	INTEGER	INTEGER
MONEY	DECIMAL	DECIMAL	The maximum number of significant digits (precision) for Transact-SQL DECIMAL is 38.
NCHAR	VARCHAR	NVARCHAR	Transact-SQL NVARCHAR has a maximum length of 4000 characters on SQL Server.
NVARCHAR	VARCHAR	NVARCHAR	Transact-SQL NVARCHAR has a maximum length of 4000 characters on SQL Server.
REAL	DOUBLE	FLOAT
SMALLDATE TIME	TIMESTAMP	DATETIME	Transact-SQL DATETIME stores values from January 1, 1753. The accuracy of dates is to one three-hundredths of a second (3.33 milliseconds).
SMALLINT	INTEGER	INTEGER
SMALLMONEY	DECIMAL	DECIMAL	The maximum number of significant digits (precision) for Transact-SQL DECIMAL is 38.
TINYINT	INTEGER	INTEGER
VARCHAR	VARCHAR	NVARCHAR	Transact-SQL NVARCHAR has a maximum length of 4000 characters on SQL Server.

Copyright Actuate Corporation 2012