Exact Synergy

Script for automated creation of scheduled e-Synergy processes (Background jobs)

Issue:

Script for automated creation of scheduled e-Synergy processes

Solution:

Explanation
Apart from the e-Synergy software, which is installed on the internet Information server, automated processes can be scheduled to run on SQL servers with e-Synergy databases. These processes perform regular maintenance, create reports and manage logon accounts.

These scheduled processes can be configured using the SQL Enterprise manager according to the Help documents, which are found in the System-Set up company menu of e-Synergy. Because creating these scheduled jobs is an elaborate, time consuming task, we've writtten an SQL script to create the jobs automatically. The processes are added with Database specific names so it is easy to find database specific processes.

This script must be run from the SQL Query Analyzer on the master database.

Changes to the script (June 26th, 2007):

• Changed schedule of BLProces from 120 minutes to every 60 minutes, every 15 past the hour, due to SQL 2005 which does not accept the 120 interval;
  

Changes to the script (May 2nd, 2005):

• Change name of 'FullText' to 'ExactFullText';
• Change schedule of ExactFullText to every 5 minutes;
• Change schedule of HrMail to every 60 minutes, every 12 past the hour;
• Only enable jobs ExactFullText, HrBgJobs, HrMail and Loghandler;
• All user variables except for the temporary drive must be set in order to eliminate mistakes;
• All jobs have the same name as the .exe-files that are executed (with an addition to similar .exe-files);
• Command lines are built up the same structure as in the help files;
• All jobs have the user 'sa' as the owner.

NOTE: Before executing the script, fill in the variables at the 'set' statements (marked in red).

Script (also attached to this document)

DECLARE @Server varchar(50)
DECLARE @Database varchar (50)
DECLARE @BaseURL varchar (50)
DECLARE @Location varchar (50)
DECLARE @TempDrive varchar (50)
DECLARE @Exchangesrv varchar (50)

DECLARE @Job varchar(150)
DECLARE @Temp varchar (150)

SET @Server = '....'        -- Name of the SQL server where the database is located  e.g. 'SERVER1'
SET @Database = '....'      -- Name of the SQL database  e.g. 'SynergyLive'
SET @BaseURL = 'http://....'       -- URL to access the local environment (portal)  e.g. 'http://SERVER1/empportal'
SET @Location = '....'      -- Local path to the Synergy installation  e.g. 'D:\Synergy' , Do not add the last slash
SET @TempDrive = 'c:\temp'     -- Local path to location for temporary files.  e.g. 'E:\Temp'
SET @Exchangesrv = ''   -- Name of the Exchange 2000 server. Leave blank if there is no Exchange server

--RPFinancialCheck
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' RPFinancialCheck'
SET @Temp = @Location + '\bin\rpfinancialcheck.exe /S:' + @Server + ' /D:' + @Database

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Validates the new imported transactions', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'ImportSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 30, @active_start_time = 011500 
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--BacoImport
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' BacoImporter'
SET @Temp = @Location + '\bin\bacoimporter.exe /S:' + @Server + ' /D:' + @Database

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Validates the new imported transactions', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'ImportSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 30, @active_start_time = 011500 
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--BacoExcelImport
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' RPExcelImport'
SET @Temp = @Location + '\bin\RPExcelImport.exe /S:' + @Server + ' /D:' + @Database

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Validates the new imported transactions', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp      
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'ExcelImportSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 30, @active_start_time = 011500 
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--HRBgJobs
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' HrBgJobs'
SET @Temp = @Location + '\bin\HrBgJobs.exe /S:' + @Server + ' /D:' + @Database + ' /NOPDC'

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Update personal and item statuses for new and exit persons', @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp   
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'HrBgJobSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 60
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--BLProcess
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' BLProcess'
SET @Temp = @Location + '\bin\BLProcess.exe /S:' + @Server + ' /D:' + @Database

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Processing of Logistics tasks (bulk validation, prolongation)', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp   
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'BLProcessSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 60, @active_start_time = 001500
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--BDScheduler - counter
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' BDSchedulerCounter'
SET @Temp = @Location + '\bin\BDScheduler.exe /S:' + @Server + ' /D:' + @Database + ' /C:Counter'

EXEC msdb..sp_add_job @job_name = @Job, @description = 'ReSETs the document counters synergy', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp   
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'BDCounterSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0, @active_start_time = 010000
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--BDScheduler - remove unused pictures
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' BDSchedulerPicture'
SET @Temp = @Location + '\bin\BDScheduler.exe /S:' + @Server + ' /D:' + @Database + ' /C:Picture'

EXEC msdb..sp_add_job @job_name = @Job, @description = 'ReSETs the document counters synergy', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp      
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'BDPictureSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0, @active_start_time = 011500
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--CustomerStatus
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' BLCustomerStatus'
SET @Temp = @Location + '\bin\BLCustomerStatus.exe /S:' + @Server + ' /D:' + @Database

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Determines the status of a customer based on contracts', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'CustomerStatusSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 10, @active_start_time = 000000
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--CustomerStatusDaily
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' BLCustomerStatusDaily'
SET @Temp = @Location + '\bin\BLCustomerStatus.exe /S:' + @Server + ' /D:' + @Database + ' /M:1'

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Determines the status of a customer based on contracts', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp   
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'CustomerStatusDailySchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0, @active_start_time = 230000
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--CustomerRating
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' CRMRating'
SET @Temp = @Location + '\bin\CRMRating.exe /S:' + @Server + ' /D:' + @Database

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Determines the rating for customers and resellers', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp   
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'CustomerRatingSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0, @active_start_time = 014500
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--CompressAttachments
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' CompressItemAttachments'
SET @Temp = @Location + '\bin\CompressItemAttachments.exe /S:' + @Server + ' /D:' + @Database + ' /DP:' + @TempDrive

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Compresses the attachments of items in Logistics', @enabled = 0, @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp   
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'CompressItemsSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0, @active_start_time = 020000
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--HrMail
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' HrMail'
SET @Temp = @Location + '\bin\HrMail.exe /S:' + @Server + ' /D:' + @Database + ' /BR:"' + @Location + '" /IBU:' + @BaseURL + ' /MP:' + @TempDrive

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Processing of HR and CRM Bulk mail tasks', @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'HrMail', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 60, @active_start_time = 001200
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--ExactFullText
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' ExactFullText'
SET @Temp = @Location + '\bin\ExactFullText.exe /S:' + @Server + ' /D:' + @Database

EXEC msdb..sp_add_job @job_name = @Job, @description = 'FullText indexing of Documents and Requests', @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'FullText Documents', @subsystem = 'CMDEXEC', @command = @Temp
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'FullText', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 5, @active_start_time = 002000
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'

--SynergyExchange
IF @Exchangesrv <> '' AND @Exchangesrv <> '...' BEGIN
  SET @Job = '(' + LEFT(@Database, 100) + ')' + ' SynergyExchange'
  SET @Temp = @Location + '\bin\SynergyExchange.exe /S:' + @Server + ' /D:' + @Database + ' /E:' + @Exchangesrv

  EXEC msdb..sp_add_job @job_name = @Job, @description = 'Synchronises e-Synergy calender with Exchange 2000 mail system', @enabled = 0, @owner_login_name = N'sa'
  EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp
  EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'SynergyExchange', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15, @active_start_time = 000000
  EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
END

--Loghandler
SET @Job = '(' + LEFT(@Database, 100) + ')' + ' Loghandler'
SET @Temp = @Location + '\bin\Loghandler.exe /S:' + @Server + ' /D:' + @Database

EXEC msdb..sp_add_job @job_name = @Job, @description = 'Truncate log data, and fill historic tables from log data', @owner_login_name = N'sa'
EXEC msdb..sp_add_jobstep @job_name = @Job, @step_name = 'Step1', @subsystem = 'CMDEXEC', @command = @Temp
EXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'LoghandlerSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0, @active_start_time = 010000
EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)' 

More information about background jobs

 !!  To be able to download the script for automated creation of scheduled Exact e-Synergy processes, please click on the document number below.