nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Where would I find the name of a business process schedule?

Rich White

In CRM, business processes can have job schedules. It appears to me that these schedules are found in the BUSINESSPROCESSSCHEDULE and SCHEDULE tables. However, neither of these tables includes a NAME or DESCRIPTION field ... where would I find the name of a business process schedule? For example, if I add a job schedule to an export process, I enter a name for the schedule but I don't see where that schedule name is stored in the DB so I think I am missing a piece of the puzzle in terms of understanding the full schema related to business process schedules.

Thanks for any help/insight,

Rich

Find more posts tagged with

Comments

Cayce Adams

I think the BUSINESSPROCESSCATALOG table is what you're looking for.

Rich White

Hi Cayce, thank you for the response! I believe the BUSINESSPROCESSCATALOG has the information for each business process, including the NAME and DESCRIPTION of the business process, however, my question is specific to the schedules that can be attached/associated with each business process. The BUSINESSPROCESSSCHEDULE table appears to be the 1-to-many cross reference table that associates business processes from BUSINESSPROCESSCATALOG with schedules from the SCHEDULE table. Since a business process can have multiple schedules attached to it and since each schedule can be given its own unique name that is independent of the name of the business process, it seems to me that there must be another place where the schedule names are stored... does that make sense?

Cayce Adams

I'm throwing darts so this may not be it either. Check out the code behind this SP: USP_BUSINESSPROCESSJOBSCHEDULE_GETLIST

select sysjobschedules.job_id,

sysschedules.schedule_id,

sysjobs.name,

sysjobsteps.command,

sysschedules.enabled,

sysschedules.freq_type,

sysschedules.freq_interval,

sysschedules.freq_subday_type,

sysschedules.freq_subday_interval,

sysschedules.freq_relative_interval,

sysschedules.freq_recurrence_factor,

sysschedules.active_start_date,

sysschedules.active_end_date,

sysschedules.active_start_time,

sysschedules.active_end_time,

sysschedules.date_created,

sysschedules.date_modified,

sysjobsteps.subsystem,

sysjobsteps.step_id

from msdb.dbo.sysschedules

inner join msdb.dbo.sysjobschedules on sysschedules.schedule_id = sysjobschedules.schedule_id

inner join msdb.dbo.sysjobsteps on sysjobschedules.job_id = sysjobsteps.job_id

inner join msdb.dbo.sysjobs on sysjobschedules.job_id = sysjobs.job_id

inner join msdb.dbo.sysproxies on sysjobsteps.proxy_id = sysproxies.proxy_id

where sysproxies.name like 'Blackbaud % Proxy - ' + db_name()

and sysjobsteps.step_name = 'Business process invoke'

and (@DATABASENAME is null or sysjobsteps.command like '%DatabaseName=' + @DATABASENAME + '&%')

and (@BUSINESSPROCESSID is null or sysjobsteps.command like '%' + @BUSINESSPROCESSID + '%');

Rich White

This looks interesting ... I will dig further in this direction - thanks!

Gethin James

I'm looking at schedules at the moment due to an issue with emails.

One thing I've noted is that the BUSINESSPROCESSSCHEDULE table and the Job Schedules tab in the Businesses Processes section aren't related as far as I can see.

The Job Schedules are SQL Server Agent Jobs and the "Job Name" is the Agent Job name. See screenshots below from CRM and SSMS respectively. If not a sysadmin you need different permissions to see the SQL Agent stuff, including permissions on the MSDB database.

There are however some business processes that do run using the BusinessProcessSchedule and Schedule tables (as far as i can see these aren't configurable anywhere in the front end) the particular problem I'm trying to solve is what actually fires them off!

Rich White

Yes, I was subsequently informed by Blackbaud that the job schedules that are created for business processes on the front end get translated into SQL server agent jobs with the corresponding names of the job schedules. Since we are hosted, I don't have the rights to see the SQL server agent jobs and wasn't able to connect all the dots.

Ernest Noa

@Rich White this thread has been very helpful. I figured out that the SCHEDULE name is stored in a table in MSDB but was hoping it was also in the main blackbaud DB. I've spent most of my morning hunting for it. I appreciate that you posted about this here. Thank you.