Sometimes strange things happen, and don’t ask me why because I don’t have a good answer to give you guys. Last week, I was troubleshooting an issue when I noticed that the DTA Purge and Archive (BizTalkDTADb) Job was not running correctly. When I checked the job history, I saw the following error:
Executed as user: NT SERVICE\SQLAgent$BIZTALK. ‘GetUTCDate1‘ is not a recognized built-in function name. [SQLSTATE 42000] (Error 195). The step failed.
At first glance, I was surprised. The SQL statement looked fine and at it was expected:
declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate1() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup, 1
Cause
The DTA Purge and Archive (BizTalkDTADb) Job automates the archiving of tracked messages and purging the BizTalk Tracking database to maintain a healthy system and keep the tracking data archived for future use.
By default, it’s configured to call the stored procedure dtasp_BackupAndPurgeTrackingDatabase, which uses the six parameters you must configure in this job:
- @nLiveHours: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. The default is 0 hours.
- @nLiveDays: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. The default interval is 1 day.
- @nHardDeleteDays: All data (even if incomplete) older than this will be deleted. The time interval specified for HardDeleteDays should be greater than the live data window. The live data window is the interval you want to maintain tracking data in the BizTalk Tracking (BizTalkDTADb) database. Anything older than this interval can be archived and purged at the next archive. The default is 30 days.
- @nvcFolder: Folder in which to put the backup files.
- @nvcValidatingServer: Server on which validation will be done. A null value indicates no validation is being done. The default is NULL.
- @fForceBackup: Default is 0. This is reserved for future use.
The original script after installing BizTalk looks like this:
execdtasp_BackupAndPurgeTrackingDatabase0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.null, --@nvcFolder nvarchar(1024) = null, --folder for backup filesnull, --@nvcValidatingServer sysname = null,0 --@fForceBackup int = 0 --
This means that:
- Any completed instance older than the live days plus live hours will be deleted, as will any associated data.
- Any data older than the HardDeleteDays will be deleted.
Normally, I use these configurations for production environments:
execdtasp_BackupAndPurgeTrackingDatabase 0, 10, 20, '...destination path...', null, 0
However, we don’t need to maintain the archived tracking data in a development machine, so I purge it periodically. BizTalk gives you the option to Archive and Purge the tracking data or just simply purge the data without archiving by changing the SQL Statement inside DTA Purge and Archive (BizTalkDTADb) Job to:
declare@dtLastBackup datetime set@dtLastBackup = GetUTCDate() execdtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup
So, why this was failing?
Due to a spelling error, at first glance it may be difficult to understand, but at the end of the GetUTCDate function there is an extra 1.
Solution
The solution to this problem is quite simple. Go to the Job and change the expression to be
- ‘GetUTCDate‘
Instead of:
- ‘GetUTCDate1‘
I hope you find this helpful! If you liked the content or found it useful and want to help me write more, you can buy (or help me buy) my son a Star Wars Lego!
