locked
Need to schedule and SQL query that will update the database RRS feed

  • Question

  • User539757411 posted

    Hi,

    I have a document, that is inserted into the SQL Server Database.
    But if the date of the document is equals today or greater, it will stay with an "UNPUBLISHED" status on database field, upon insertion.

    If server time is greater then 12PM, so the document should change its status to "PUBLISHED" status on database field.

    Does SQL Server has an scheduler or  IIS or .. ? How can i schedule that query that will update the database, everyday, at 12:01PM ?

    Thanks in advance!

    Monday, June 20, 2016 6:22 PM

Answers

  • User1559292362 posted

    Hi,

    As deepalgorith said, we could create a SQL Agent Job to achieve it. We could use SSMS to create a job (deepalgorith has provided), we could also use T-SQL to create a job.

    #create a SQL Server Job Using T-SQL

    1. Creating a Stored Procedure to create SQL Agent jobs

    USE msdb
    go
    CREATE procedure [dbo].[sp_add_job_quick] 
    @job nvarchar(128),
    @mycommand nvarchar(max), 
    @servername nvarchar(28),
    @startdate nvarchar(8),
    @starttime nvarchar(8)
    as
    --Add a job
    EXEC dbo.sp_add_job
        @job_name = @job ;
    --Add a job step named process step. This step runs the stored procedure
    EXEC sp_add_jobstep
        @job_name = @job,
        @step_name = N'process step',
        @subsystem = N'TSQL',
        @command = @mycommand
    --Schedule the job at a specified date and time
    exec sp_add_jobschedule @job_name = @job,
    @name = 'MySchedule',
    @freq_type=1,
    @active_start_date = @startdate,
    @active_start_time = @starttime
    -- Add the job to the SQL Server Server
    EXEC dbo.sp_add_jobserver
        @job_name =  @job,
        @server_name = @servername
    

    2.  invoke the stored procedure in order to create the job:

    exec dbo.sp_add_job_quick 
    @job = 'myjob', -- The job name
    @mycommand = 'sp_who', -- The T-SQL command to run in the step
    @servername = 'serverName', -- SQL Server name. If running localy, you can use @servername=@@Servername
    @startdate = '20160621', -- The date June 21th, 2016
    @starttime = '000000' -- The time, 00:00:00
    

    For more information, please refer to:

    https://www.mssqltips.com/sqlservertip/3052/simple-way-to-create-a-sql-server-job-using-tsql/

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 21, 2016 3:34 AM

All replies

  • User765422875 posted

    Yes you can create a schedule job.

    Create a schedule job that will run the query on a specific schedule.

    To create and attach a schedule to a job

    • In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
    • Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
    • Select the Schedules page, and then click New.

    https://msdn.microsoft.com/en-us/library/ms191439.aspx

    Monday, June 20, 2016 6:24 PM
  • User1559292362 posted

    Hi,

    As deepalgorith said, we could create a SQL Agent Job to achieve it. We could use SSMS to create a job (deepalgorith has provided), we could also use T-SQL to create a job.

    #create a SQL Server Job Using T-SQL

    1. Creating a Stored Procedure to create SQL Agent jobs

    USE msdb
    go
    CREATE procedure [dbo].[sp_add_job_quick] 
    @job nvarchar(128),
    @mycommand nvarchar(max), 
    @servername nvarchar(28),
    @startdate nvarchar(8),
    @starttime nvarchar(8)
    as
    --Add a job
    EXEC dbo.sp_add_job
        @job_name = @job ;
    --Add a job step named process step. This step runs the stored procedure
    EXEC sp_add_jobstep
        @job_name = @job,
        @step_name = N'process step',
        @subsystem = N'TSQL',
        @command = @mycommand
    --Schedule the job at a specified date and time
    exec sp_add_jobschedule @job_name = @job,
    @name = 'MySchedule',
    @freq_type=1,
    @active_start_date = @startdate,
    @active_start_time = @starttime
    -- Add the job to the SQL Server Server
    EXEC dbo.sp_add_jobserver
        @job_name =  @job,
        @server_name = @servername
    

    2.  invoke the stored procedure in order to create the job:

    exec dbo.sp_add_job_quick 
    @job = 'myjob', -- The job name
    @mycommand = 'sp_who', -- The T-SQL command to run in the step
    @servername = 'serverName', -- SQL Server name. If running localy, you can use @servername=@@Servername
    @startdate = '20160621', -- The date June 21th, 2016
    @starttime = '000000' -- The time, 00:00:00
    

    For more information, please refer to:

    https://www.mssqltips.com/sqlservertip/3052/simple-way-to-create-a-sql-server-job-using-tsql/

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 21, 2016 3:34 AM
  • User-595703101 posted

    Hi xandeq,

    You can create SQL Server job which will execute periodically at 12:00 everyday.

    When the job is executed, you can call a stored procedure which updates the status of your database record to published in your application's logic.

    So your application can display the updated records and display it on the UI

    Tuesday, June 21, 2016 6:07 AM