Click here to Skip to main content
15,885,278 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
this job must run every 20 second for database roomanado
exec [xxbb].[NatureOfSuit_Importer] ''\\192.168.20.7\Import\NoOfSuitImporter_input.xlsx + '',''\\192.168.20.7\Import\NoOfSuitImporter_output.xlsx''
i dont need to use schedule wizard
i need to execute stored procedure command below every 20 seconds from new query
Are code below correct for execute stored procedure from new query ?

What I have tried:

USE msdb;
GO

DECLARE @jobId binary(16);

EXEC msdb.dbo.sp_add_job
    @job_name = N'Run for 15 seconds'
,   @enabled = 1
,   @notify_level_eventlog = 0
,   @notify_level_email = 2
,   @notify_level_page = 2
,   @delete_level = 0
,   @category_name = N'[Uncategorized (Local)]'
,   @owner_login_name = N'sa'
,   @job_id = @jobId OUTPUT;

SELECT
    @jobId;
GO

EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'Run for 15 seconds'
,   @server_name = N'192.168.2.8';
GO

USE msdb;
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Run for 15 seconds'
,   @step_name = N'Wait for it'
,   @step_id = 1
,   @cmdexec_success_code = 0
,   @on_success_action = 1
,   @on_fail_action = 2
,   @retry_attempts = 1
,   @retry_interval = 15
,   @os_run_priority = 1
,   @subsystem = N'TSQL'
,   @command = N'exec [Litigation].[NatureOfSuit_Importer] ''\\192.168.2.7\Import\fath\Importers\NoOfSuitImporter_input.xlsx + '',''\\192.168.2.7\Import\fath\Importers\NoOfSuitImporter_output.xlsx'''
,   @database_name = N'Z2DataNoneFinancial'
,   @flags = 0;
GO

USE msdb;
GO

EXEC msdb.dbo.sp_update_job
    @job_name = N'Run for 15 seconds'
,   @enabled = 1
,   @start_step_id = 1
,   @notify_level_eventlog = 0
,   @notify_level_email = 2
,   @notify_level_page = 2
,   @delete_level = 0
,   @description = N''
,   @category_name = N'[Uncategorized (Local)]'
,   @owner_login_name = N'sa'
,   @notify_email_operator_name = N''
,   @notify_page_operator_name = N'';
Posted
Updated 3-Nov-19 2:38am
Comments
Richard MacCutchan 3-Nov-19 7:44am    
The best way to find out if your code is correct is to build and run it yourself.
RickZeeland 3-Nov-19 8:35am    
Are you using SQL Server Express ?

1 solution

Maybe this will be helpful: Simple way to create a SQL Server Job Using T-SQL[^]

Also I think you are missing sp_add_jobschedule, see example here: command line - How do I create a SQL Server agent without using a GUI? - Stack Overflow[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900