Script for automated creation of scheduled e-Synergy processes
ExplanationApart 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):
Changes to the script (May 2nd, 2005):
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
--RPFinancialCheckSET @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 = @TempEXEC 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)'
--BacoImportSET @Job = '(' + LEFT(@Database, 100) + ')' + ' BacoImporter'SET @Temp = @Location + '\bin\bacoimporter.exe /S:' + @Server + ' /D:' + @Database
--BacoExcelImportSET @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)'
--HRBgJobsSET @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 = 60EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--BLProcessSET @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 = 001500EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--BDScheduler - counterSET @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 = 010000EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--BDScheduler - remove unused picturesSET @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 = 011500EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--CustomerStatusSET @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 = @TempEXEC 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 = 000000EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--CustomerStatusDailySET @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 = 230000EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--CustomerRatingSET @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 = 014500EXEC 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 = 020000EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--HrMailSET @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 = 001200EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--ExactFullTextSET @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 = 002000EXEC msdb..sp_add_jobserver @job_name = @Job, @server_name = '(local)'
--SynergyExchangeIF @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
--LoghandlerSET @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 = @TempEXEC msdb..sp_add_jobschedule @job_name = @Job, @name = 'LoghandlerSchedule', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 0, @active_start_time = 010000EXEC 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.