Useful sql to create the e-Synergy background jobs

[canimp]

hopefully, you will benefit from the following .sql which sets up the e-Synergy background jobs automatically - saves a lot of time & effort.

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.

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



NOTE: Before executing the script, fill in the variables at the 'set' statements.




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

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='...' -- 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='...' -- Local path to location for temporary files. e.g. 'E:\Temp'
set @Exchangesrv='...' -- Name of the Exchange 2000 server

--BacoImport

EXEC msdb..sp_add_job @job_name='BacoImport',
@description='Validates the new imported transactions'

set @Temp=@Location+'\bin\bacoimporter.exe /S:'+@Server+' /D:'+@Database

Exec msdb..sp_add_jobstep @job_name='BacoImport',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='BacoImport',
@name='ImportSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@active_start_time=011500


--BacoExcelImport

EXEC msdb..sp_add_job @job_name='BacoExcelImport',
@description='Validates the new imported transactions'

set @Temp=@Location+'\bin\RPExcelImport.exe /S:'+@Server+' /D:'+@Database

Exec msdb..sp_add_jobstep @job_name='BacoExcelImport',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='BacoExcelImport',
@name='ExcelImportSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@active_start_time=011500




--HRBgJobs

EXEC msdb..sp_add_job @job_name = 'HrBgJobs',
@description = 'Update personal and item statuses for new and exit persons'

set @Temp=@Location+'\bin\HrBgJobs.exe /S:'+@Server+' /D:'+@Database+' /NOPDC'

Exec msdb..sp_add_jobstep @job_name='HrBgJobs',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='HrBgJobs',
@name='HrBgJobSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=60


--BLProcess

EXEC msdb..sp_add_job @job_name = 'BLProcess',
@description = 'Processing of Logistics tasks (bulk validation, prolongation)'

set @Temp=@Location+'\bin\BLProcess.exe /S:'+@Server+' /D:'+@Database

Exec msdb..sp_add_jobstep @job_name='BLProcess',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='BLProcess',
@name='BLProcessSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=120,
@active_start_time=001500


--BDScheduler - counter

EXEC msdb..sp_add_job @job_name = 'BDCounterScheduler',
@description = 'Resets the document counters synergy'

set @Temp=@Location+'\bin\BDScheduler.exe /S:'+@Server+' /D:'+@Database+' /C:Counter'

Exec msdb..sp_add_jobstep @job_name='BDCounterScheduler',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='BDCounterScheduler',
@name='BDCounterSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0,
@active_start_time=010000


--BDScheduler - remove unused pictures

EXEC msdb..sp_add_job @job_name = 'BDPictureScheduler',
@description = 'Resets the document counters synergy'

set @Temp=@Location+'\bin\BDScheduler.exe /S:'+@Server+' /D:'+@Database+' /Cicture'

Exec msdb..sp_add_jobstep @job_name='BDPictureScheduler',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='BDPictureScheduler',
@name='BDPictureSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0,
@active_start_time=011500


--CustomerStatus

EXEC msdb..sp_add_job @job_name = 'CustomerStatus',
@description = 'Determines the status of a customer based on contracts'

set @Temp=@Location+'\bin\BLCustomerStatus.exe /S:'+@Server+' /D:'+@Database

Exec msdb..sp_add_jobstep @job_name='CustomerStatus',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='CustomerStatus',
@name='CustomerStatusSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=10,
@active_start_time=000000

--CustomerStatusDaily

EXEC msdb..sp_add_job @job_name = 'CustomerStatusDaily',
@description = 'Determines the status of a customer based on contracts'

set @Temp=@Location+'\bin\BLCustomerStatus.exe /S:'+@Server+' /D:'+@Database+' /M:1'

Exec msdb..sp_add_jobstep @job_name='CustomerStatusDaily',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='CustomerStatusDaily',
@name='CustomerStatusDailySchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0,
@active_start_time=230000


--CustomerRating

EXEC msdb..sp_add_job @job_name = 'CustomerRating',
@description = 'Determines the rating for customers and resellers'

set @Temp=@Location+'\bin\CRMRating.exe /S:'+@Server+' /D:'+@Database

Exec msdb..sp_add_jobstep @job_name='CustomerRating',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='CustomerRating',
@name='CustomerRatingSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0,
@active_start_time=014500


--CompressAttachments

EXEC msdb..sp_add_job @job_name = 'CompressItemAttachments',
@description = 'Compresses the attachments of items in Logistics'

set @Temp=@Location+'\bin\CompressItemAttachments.exe /S:'+@Server+' /D:'+@Database+' /DP:'+@TempDrive

Exec msdb..sp_add_jobstep @job_name='CompressItemAttachments',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='CompressItemAttachments',
@name='CompressItemsSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0,
@active_start_time=020000


--HRMailer

EXEC msdb..sp_add_job @job_name = 'HrMailer',
@description = 'Processing of HR and CRM Bulk mail tasks'

set @Temp=@Location+'\bin\HrMail.exe /S:'+@Server+' /D:'+@Database+' /BR:'+@Location+' /IBU:"'+@BaseURL+'"'+' /MP:'+@TempDrive

Exec msdb..sp_add_jobstep @job_name='HrMailer',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='HrMailer',
@name='HrMailer',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=120,
@active_start_time=002000

--FullText

EXEC msdb..sp_add_job @job_name = 'FullText',
@description = 'FullText indexing of Documents and Requests'

set @Temp=@Location+'\bin\ExactFullText.exe /S:'+@Server+' /D:'+@Database

Exec msdb..sp_add_jobstep @job_name='FullText',
@step_name='FullText Documents',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='FullText',
@name='FullText',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=65,
@active_start_time=002000

--SynergyExchange

If @Exchangesrv <> '' and @Exchangesrv <> '...'
BEGIN

EXEC msdb..sp_add_job @job_name='SynergyExchange',
@description='Synchronises e-Synergy calender with Exchange 2000 mail system'

set @Temp=@Location+'\bin\SynergyExchange.exe /S:'+@Server+' /D:'+@Database+' /E:'+@Exchangesrv

Exec msdb..sp_add_jobstep @job_name='SynergyExchange',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='SynergyExchange',
@name='SynergyExchange',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@active_start_time=000000

END

--Loghandler

EXEC msdb..sp_add_job @job_name = 'Loghandler',
@description = 'Truncate log data, and fill historic tables from log data'

set @Temp=@Location+'\bin\Loghandler.exe /S:'+@Server+' /D:'+@Database

Exec msdb..sp_add_jobstep @job_name='Loghandler',
@step_name='Step1',
@subsystem='CMDEXEC',
@command=@Temp

Exec msdb..sp_add_jobschedule @job_name='Loghandler',
@name='LoghandlerSchedule',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0,
@active_start_time=010000


Exec msdb..sp_add_jobserver @job_name='BacoImport',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='BacoExcelImport',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='CompressItemAttachments',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='CustomerStatus',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='CustomerStatusDaily',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='CustomerRating',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='HrBgJobs',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='BLProcess',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='BDCounterScheduler',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='BDPictureScheduler',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='HrMailer',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='FullText',
@server_name='(local)'
Exec msdb..sp_add_jobserver @job_name='Loghandler',
@server_name='(local)'
If @Exchangesrv <> '' and @Exchangesrv <> '...'
BEGIN
Exec msdb..sp_add_jobserver @job_name='SynergyExchange',
@server_name='(local)'
END