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

  • 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 ?
    Sunday, November 3, 2019 8:32 AM

Answers

  •  

    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 MSDNFSF@microsoft.com.

    Wednesday, November 6, 2019 9:50 AM

All replies

  • 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

    Sunday, November 3, 2019 9:00 AM
    Answerer
  • 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'';

    Sunday, November 3, 2019 9:08 AM
  • 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


    Sunday, November 3, 2019 9:20 AM
    Answerer
  • 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 

    Sunday, November 3, 2019 9:32 AM
  • >>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 commandsubsystem 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

    Sunday, November 3, 2019 9:37 AM
    Answerer
  • 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 MSDNFSF@microsoft.com.

    Monday, November 4, 2019 3:28 AM
  •  

    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 MSDNFSF@microsoft.com.

    Wednesday, November 6, 2019 9:50 AM