Sometimes, strange things happen, and I honestly can’t explain why. Last week, while troubleshooting an issue, I noticed that the DTA Purge and Archive (BizTalkDTADb) SQL job was not running as expected. To investigate further, I opened the job history and immediately spotted 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 it was expected:
declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate1() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup, 1
📝 One-Minute Brief
BizTalk Server relies heavily on SQL Server Agent jobs for platform maintenance and monitoring. After upgrading SQL Server or changing database compatibility levels, these jobs may suddenly fail with the error “GETUTCDATE() is not a recognized built‑in function name.” This article explains why this error occurs, how SQL Server compatibility impacts BizTalk jobs, and the steps required to fix the issue so BizTalk background processes can run successfully again.
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 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 was this 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!
