SQL-005: clsAppErrorCollection.RetrieveSQL

This query returns the page of matching App_Error rows for an error-log search. It supports date filtering, optional date ranges, machine and application filters, user filtering, multi-value environment/user expansion, full-text search over the error text columns, and caller-controlled ordering.

Classification

Field	Value
SQL ID	SQL-005
Operation type	Read
Execution method	Inline
Database	SQL Server
Schema	[unqualified]
Command type	Text
Business object	clsAppErrorCollection
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve (DataRequest1)

Parameters

Parameter	Direction	Type	Max length	Description
@ErrorDate	Input	DateTime	N/A	Exact-date filter when a single error date is supplied instead of a range.
@MachineName	Input	String	50	Machine-name filter value.
@Username	Input	String	50	User-name filter value used for LIKE matching or paired with a generated IN (...) branch.
@AppName	Input	String	50	Application-name filter value.
@SEARCHTEXT	Input	String	100	Full-text predicate assembled by GetSearchValue().

Tables

Table	Access	Columns used
App_Error	SELECT	App_Error_Seq_Num, App_Name, Deleted_Flag, Environment, Error_Date, Error_Info, Error_Source, Machine_Name, Page_Name, State_Info, User_Name

Sample SQL

SELECT top 300
    App_Error.App_Error_Seq_Num as AppErrorSeqNum,
    App_Error.App_Name as AppName,
    App_Error.Deleted_Flag as DeletedFlag,
    App_Error.Environment as Environment,
    App_Error.Error_Date as ErrorDate,
    App_Error.Error_Info as ErrorInfo,
    App_Error.Error_Source as ErrorSource,
    App_Error.Machine_Name as MachineName,
    App_Error.Page_Name as PageName,
    App_Error.State_Info as StateInfo,
    App_Error.User_Name as UserName
FROM App_Error
WHERE cast(cast(App_Error.Error_Date as varchar(11))as datetime) = @ErrorDate
  and App_Error.Machine_Name = @MachineName
  and App_Error.App_Name = @AppName
  and App_Error.User_Name like @UserName
  AND CONTAINS((App_Error.Error_Info,App_Error.State_Info,App_Error.Error_Source),@SEARCHTEXT)
ORDER BY Error_Date DESC

Operations and processes

• Search application error logs
• Normalize free-text search terms

Business rules

• BR-001: Search results are capped at 300 rows
• BR-002: All sentinel suppresses selected filters
• BR-003: Semicolon-delimited filters expand to SQL IN lists
• BR-004: Search text is normalized for full-text queries

Sources

• Application\SAIF.Application.LogView\clsAppErrorCollection.vb