How to create job from new query editor on sql server 2012 run every 20 seconds ?

Question

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 above every 20 seconds  from new query 
How to do that please which code i write to do that ?

Answer 1

Default value is 10 Seconds as you see. This is because to avoid the number of aborts of job execution if the next schedule is invoked.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

thank you for reply

i need to make it using new query 

are this correct please 

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'';

Answer 3

Probably yes, create a job, defined all steps and schedule for 20 sec  and just script it out....

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

I have more questions can you help me

are msdb is exist on every server

also what subsystem

category_name what must be

server name must be what ip or name of server 

Answer 5

>>are msdb is exist on every server

Yep it is a system database

>>also what subsystem

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql?view=sql-server-ver15

[ @subsystem = ] 'subsystem' The subsystem used by the SQL Server Agent service to execute command. subsystem is nvarchar(40), and can be one of these values.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 6

Hi engahmedbarbary,

 

---are msdb is exist on every server

 

Yes . SQL Server includes the following system databases:master Database ,msdb Database, model Database, Resource Database and tempdb Database. Msdb Database is used by SQL Server Agent for scheduling alerts and jobs. For more information , please refer to msdb Database.

 

---also what subsystem

 

Please check Uri Dimant's advice .

 

---also what subsystem

[ @category_name = ] 'category' The category for the job. categoryis sysname, with a default of NULL. For more information, please refer to sp_add_job (Transact-SQL)

 

Hope it will help you.

 

Best Regards,

Rachel

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

 

Hi ,

 

I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

 

Best Regards,

Rachel

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].