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

Rich White
Rich White
Tenth Anniversary Name Dropper Participant Facilitator 1
in Blackbaud CRM Developer Discussions
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

Comments

  • Cayce Adams
    Cayce Adams
    Sixth Anniversary Participant Facilitator 1 Loyalty Badge
    I think the BUSINESSPROCESSCATALOG table is what you're looking for.
  • Rich White
    Rich White
    Tenth Anniversary Name Dropper Participant Facilitator 1
    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
    Cayce Adams
    Sixth Anniversary Participant Facilitator 1 Loyalty Badge
    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
    Rich White
    Tenth Anniversary Name Dropper Participant Facilitator 1
    This looks interesting ... I will dig further in this direction - thanks!
  • Gethin James
    Gethin James
    Sixth Anniversary Name Dropper Facilitator 1
    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
    Rich White
    Tenth Anniversary Name Dropper Participant Facilitator 1
    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
    Ernest Noa
    Fifth Anniversary Kudos 1 Name Dropper Participant

    @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.

Categories