Click here to Skip to main content
14,385,111 members
Rate this:
Please Sign up or sign in to vote.
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 3: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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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[^]
   
v2

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100