Searching for blank fields with Additional Search Arguments results in Error AHD04199.

Document ID : KB000049269
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

This article explains how to Search for fields which are empty (NULL) when running Service Desk 12.5 CA SDM Service Pack 1 and above?

On SDM 12.5 CA SDM GA and below, this string is valid:

external_system_ticket = ''

However, when used with SDM 12.5 CA SDM SP1 or above, it results in this on-screen error:

AHD04199:An unexpected error occurred. Contact your administrator. [Microsoft OLE DB Provider for SQL Server] [ SQL Code=402 SQL State=42000] The data types ntext and nvarchar are incompatible in the equal to operator. (active = 1 AND ((external_system_ticket ='')))

Solution:

ADDITIONAL SEARCH ARGUMENTS AND STORED QUERIES

An architectural change has been implemented in SDM 12.5 CA SDM Service Pack 1 (and above, including SDM 12.6 CA SDM) which requires a change of syntax for Additional Search Arguments, Stored Queries and the like when searching for string fields that have no value.

To return all tickets where external_system_ticket is 'empty' (NULL), use this clause:

external_system_ticket IS NULL

To return all tickets where external_system_ticket has a value of "123", use this clause:

external_system_ticket LIKE '123'

If the old syntax of:

external_system_ticket LIKE ''

is used, the following error is displayed:

AHD04199:An unexpected error occurred. Contact your administrator. [Microsoft OLE DB Provider for SQL Server] [ SQL Code=402 SQL State=42000] The data types ntext and nvarchar are incompatible in the equal to operator. (active = 1 AND ((external_system_ticket ='')))

GO BUTTON RESOURCE

The Go Button Resource is impacted by the architectural change in a specific way.

On SDM 12.5 CA SDM GA, searches originating from the Go button resource always use the LIKE operator. In SDM 12.5 CA SDM SP1, these searches have been modified and now use the equals operator "=" instead of the LIKE operator when the wildcard character ( % ) is not present in the search. This modification was done to resolve performance related problems seen when searching for tickets. The LIKE operator may cause slow performance on certain Go Button searches. For SP1 and above, searches originating from the Go Button resource now use the equals operator when the wildcard character is not present.

When a database field has an associated field type of ntext, such as for the external_system_ticket field, the LIKE operator should be used in the where clause, because the database does not permit the use of the equals operator in this case. To cause the LIKE operator to be used, add a wildcard character "%" to the search originating from the Go Button. You may modify the Resource field for the "IN - External System Ticket" web form to be one of the following, depending on where you want to place the wildcard:

  $cgi?OP=SEARCH+SID=$SESSION.SID+FID=123+FACTORY=in+SKIPLIST=1+QBE.IN.external_system_ticket=%25$searchKey 
  or 
  $cgi?OP=SEARCH+SID=$SESSION.SID+FID=123+FACTORY=in+SKIPLIST=1+QBE.IN.external_system_ticket=$searchKey%25 
  or 
  $cgi?OP=SEARCH+SID=$SESSION.SID+FID=123+FACTORY=in+SKIPLIST=1+QBE.IN.external_system_ticket=%25$searchKey%25 

Note, in the above examples, that "%25" is being used - It is the URL encoding for the percent sign (" %") which is used as the wildcard character.