FIX: DTA_PURGE_OLD_RECORDS Stored Procedure Fails with nPurgeType of Timestamp (306798)

The information in this article applies to:

Microsoft BizTalk Server 2000

This article was previously published under Q306798

SYMPTOMS

After you execute the BizTalk Tracking database stored procedure DTA_PURGE_OLD_RECORDS with an nPurgeType of timestamp and an nPurgeValue of hours, the following error message may occur in SQL Query Analyzer:

Server: Msg 535, Level 16, State 1, Procedure dta_purge_old_records, Line 103 Difference of two datetime columns caused overflow at runtime.

CAUSE

This problem is caused by the DTA_PURGE_OLD_RECORDS stored procedure passing milliseconds (ms) to the TSQL DATEDIFF function instead of seconds (ss).

RESOLUTION

Manually modify the DTA_PURGE_OLD_RECORDS stored procedure to pass seconds to the TSQL DATEDIFF function. To do this:

Open SQL Enterprise Manager.
Find the DTA_PURGE_OLD_RECORDS stored procedure for the tracking database (by default the tracking database is named InterchangeDTA).
Find the line where DATEDIFF() is used (approximately line 120).
Change the first argument from ms to ss.

STATUS

Microsoft has confirmed that this is a problem in BizTalk Server 2001.

This bug was corrected in Microsoft BizTalk Server 2002.

MORE INFORMATION

Steps to Reproduce Behavior

Open SQL Query Analyzer, select the Tracking database, and then run the following SQL command:

exec DTA_PURGE_OLD_RECORDS 1,600,1,0

This command will attempt to delete records that are older than 600 hours.

Modification Type:	Major	Last Reviewed:	11/5/2003
Keywords:	kbbug kbfix KB306798