Sa T-SQL skriptom

Sa T-SQL procedurom

U slučajevima, kada nemamo SQL Managment Studio, da bi kreirali job na SQL serveru, možemo to isto da uradimo korištenjem T-SQL procedure.

Job će napraviti kompletan obračun. Ako to ne želite, onda popravite procedure u koracima (steps).
Za pravilno djelovanje potrebno je u skripti popraviti:

• servername     = SERVER    (ime servera, na kojem radimo job odn. na njemu je baza podataka)
• databasename = msDataLab     (ime baze podataka, za koju radimo obračun prometa)

Upisane vrijednosti (italic i bold) je potrebno promjeniti sa imenom Vašeg servera i Vaše baze podataka, to najlakše popravite s find/replace funkcijom pri upisivanju skripte.

Programski kod za skriptu, koji nam kreira takav job je naveden ispod:

begin transaction

declare
@JobID binary(16),
@ReturnCode int

set nocount on
select @ReturnCode = 0

-- kreiraj glavo joba
execute @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output,
@job_name = N'Preračun prometov - vsak dan', @enabled = 1,
@description = N'',
@category_name = N'[Uncategorized (Local)]', 
@notify_level_eventlog = 2, @delete_level= 0
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

-- kreiraj step 1 - Preračunaj serijske
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1, @step_name = N'Preračunaj serijske',
@command = N'exec pPA_SerialNoItemRecalc '''', null, null
exec pPA_SerialNoStatusRecalc ''''', @database_name = N'msDataLab',
@server = N'', @database_user_name = N'', @subsystem = N'TSQL',
@cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0,
@retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0,
@on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

-- kreiraj step 2 - Preračunaj cene
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 2, @step_name = N'Preračunaj cene',
@command = N'exec pPA_PriceCalc '''', null, null, ''''
exec pPA_PriceCalcOwnPrice '''', null, null, ''''
exec pPA_MoveAssmblyRecalc ''''',
@database_name = N'msDataLab', @server = N'', 
@database_user_name = N'',@subsystem = N'TSQL',
@cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0,
@retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0,
@on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

-- kreiraj step 3 - Obnovi zalogo
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 3, @step_name = N'Obnovi zalogo',
@command = N'exec pPA_StockRecalc ''''',
@database_name = N'msDataLab', @server = N'', 
@database_user_name = N'', @subsystem = N'TSQL',
@cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0,
@retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0,
@on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

-- kreiraj step 4 - Preračunaj glave dokumentov
execute @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 4, @step_name = N'Preračunaj glave dokumentov',
@command = N'exec pPA_CashRegRecalc null, null
exec pPA_GLRecalc null, null
exec pPA_OrderHeadRecalc null, null
exec pPA_OrderDispDocRecalc '''', null, null
exec pPA_MoveHeadRecalc null, null, ''F''',
@database_name = N'msDataLab', @server = N'', 
@database_user_name = N'', @subsystem = N'TSQL',
@cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0,
@retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0,
@on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

-- zapiši začetni korak v job
execute @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

-- postavi schedule izvajanja
execute @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = N'Vsakodnevno izvajanje ob 01.00', @enabled = 1,
@freq_type = 4, @active_start_date = 20020103,
@active_start_time = 10000, @freq_interval = 1,
@freq_subday_type = 1, @freq_subday_interval = 0,
@freq_relative_interval = 0, @freq_recurrence_factor = 1,
@active_end_date = 99991231, @active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

-- daj job v izvajanje na strežnik
execute @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'STREŽNIK', @automatic_post = 0
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

select @JobID
if (@@ERROR <> 0 OR @ReturnCode <> 0)
goto QuitWithRollback

commit transaction
goto EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) rollback transaction
EndSave:

I kod ove skripte važe sve napomene, koje smo zapisali u kreiranju obračuna prometa sa SQL Server Managment Studiom.