Aug 122008

On this BizTalk Glossary Page it states that a zombie ressurector is: An orchestration that listens at the MessageBox database for zombies and resurrects those that meet certain criteria.

The previous definition of a zombie is: A message that was not processed by an orchestration. It is not in the Suspended queue or anywhere else where it will be retried.

If there is an orchestration that is looking for ‘zombies’ – then they were never zombies were they?

Once you have answered that, then you can answer the question of which came first, the chicken or the egg?

Aug 122008

I have needed for some SQL jobs to run every second. I went into the Jobs and started to configure the job, unfortunately, the lowest value I can choose is minute:


So I found that in the script, the underlying values for Occurs (@freq_subday_interval) every are as follow:


Value Description (unit)
1 At the specified time
2 Seconds
4 Minutes
8 Hours

So I scripted the schedule like this:

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'EverySecond', @enabled=1, @freq_type=4, @freq_interval=4, @freq_subday_type=2, @freq_subday_interval=1, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20080812, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959

I got the following error:

Msg 14278, Level 16, State 1, Procedure sp_verify_schedule, Line 214

The schedule for this job is invalid (reason: The specified ‘@freq_subday_interval’ is invalid.).

Here is the logic in the sp_verify_schedule

IF ((@freq_subday_type <> 0x1) AND (@freq_subday_interval < 1)) OR ((@freq_subday_type = 0x2) AND (@freq_subday_interval < 10)) BEGIN SELECT @reason = FORMATMESSAGE(14200, '@freq_subday_interval') RAISERROR(14278, -1, -1, @reason) RETURN(1) -- Failure END

So there are a few things that can be done to get around it:

In the job use the following code:

EXEC stored_proc; WAITFOR DELAY '00:00:01'; EXEC stored_proc; -- again WAITFOR DELAY '00:00:01'; EXEC stored_proc; -- and again

Or by tracking through the various stored procedures (sp_add_jobschedule and sp_add_schedule) we see that the following table is where it ends up: msdb.dbo.sysschedules, so we can just go update the table after we scheduled the job.

UPDATE msdb.dbo.sysschedules SET name = ISNULL(@schedule_name, name), enabled = ISNULL(@enabled, enabled), freq_type = ISNULL(@freq_type, freq_type), freq_interval = ISNULL(@freq_interval, freq_interval), freq_subday_type = ISNULL(@freq_subday_type, freq_subday_type), freq_subday_interval = ISNULL(@freq_subday_interval, freq_subday_interval), freq_relative_interval = ISNULL(@freq_relative_interval, freq_relative_interval), freq_recurrence_factor = ISNULL(@freq_recurrence_factor, freq_recurrence_factor), active_start_date = ISNULL(@active_start_date, active_start_date), active_end_date = ISNULL(@active_end_date, active_end_date), active_start_time = ISNULL(@active_start_time, active_start_time), active_end_time = ISNULL(@active_end_time, active_end_time) WHERE schedule_uid = @schedule_uid

I decided to modify the sp_verify_schedule

IF ((@freq_subday_type <> 0x1) AND (@freq_subday_interval < 1)) OR ((@freq_subday_type = 0x2) AND (@freq_subday_interval < 1)) BEGIN SELECT @reason = FORMATMESSAGE(14200, '@freq_subday_interval') RAISERROR(14278, -1, -1, @reason) RETURN(1) -- Failure END

Did I happen to mention the disclaimer on the right side of my blog?

Aug 122008

While modifying a file for a BizTalk process for a client we kept on getting an Error using message tracking  in HAT looking like the following:

System.NullReferenceException: Object reference not set to an instance of an object.

   at Elite.EAI.Orchestrations.Receive_Orchestration.segment8(StopConditions stopOn)

   at Microsoft.XLANGs.Core.SegmentScheduler.RunASegment(Segment s, StopConditions stopCond, Exception& exp)

The only thing that overcame this problem was changing the file name to something the BizTalk application recognized. In our case that was UPS for a UPS process. Adding a GUID also threw the same exception. Adding characters to the end didn’t throw the exception.