SUMMARY
This document outlines an error seen when users try to use the upper function on TEXT data type in Microsoft SQL Server. This is a limitation from Microsoft SQL Server and not a Strategy issue.
SYMPTOM
In Strategy 9.x, when searching for an attribute element in a prompted attribute qualification, “Argument data type text is invalid for argument 1 of upper function” error is seen as shown below:
Below entry is observed in the DSSErrors.log:
[Database Classes][Error] Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [42000:8116: on SQLHANDLE] [Microsoft][ODBC SQL Server Driver][SQL Server]Argument data type text is invalid for argument 1 of upper function. Connection String: DSN=xxxxxx;UID= xxxxxx;APP=Strategy 9.4 ® Common Files;WSID= xxxxxx;DATABASE= xxxxxx;. SQL Statement: select count(a21.Attribute1) WJXBFS1
from TEST a21
where upper(a21. Attribute1) like upper('%ayam%').
CAUSE
For Microsoft SQL Server database, some functions that work for VARCHAR are not legal with TEXT, including the Upper function.
Microsoft recommends avoiding the TEXT data type. Refer to Microsoft Technical Document for more details.
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
ACTION
Users should change the data type of the column from TEXT to VARCHAR on database side.