PRB: Problem with the "sp_update_job_trigger" Stored Procedure (288241)

SYMPTOMS

A problem may occur with the following if-statement in the "sp_update_job_trigger" stored procedure:

if @newfolderid <> @oldfolderid

If "@oldfolderid" has a null value, which is the initial value for received e-mails messages, the if-statement may evaluate to false and the Userstatus value in the job table may not be updated. If Userstatus does not indicate that the message is deleted, you cannot remove messages from the trash. Internet Cellular Smart Access (ICSA) SDK Messaging (ISM) does a check on the Userstatus value in the DeleteMail method.

CAUSE

The SET ANSI_NULLS SQL option controls whether comparison with null values evaluates to true or false. The SET ANSI_NULLS SQL option is set when the stored procedure is created. This problem may occur if the SET ANSI_NULLS option is set to ON when the procedure was created. The default value is OFF for database (DB) library connections and ON for OLE DB connections.

MORE INFORMATION

For additional information about this problem, click the article number below to view the article in the Microsoft Knowledge Base:

214601 ANSI_NULLS OFF Behavior in SQL Server 6.x and 7.0