Microsoft KB Archive/306798

= FIX: DTA_PURGE_OLD_RECORDS Stored Procedure Fails with nPurgeType of Timestamp =

Article ID: 306798

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft BizTalk Server 2000 Standard Edition

-



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:
 * 1) Open SQL Enterprise Manager.
 * 2) Find the DTA_PURGE_OLD_RECORDS stored procedure for the tracking database (by default the tracking database is named InterchangeDTA).
 * 3) Find the line where DATEDIFF is used (approximately line 120).
 * 4) 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.



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.

Keywords: kbbug kbfix KB306798

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.