Getting started with Microsoft SQL ServerOVER ClausePIVOT / UNPIVOTDatabase SnapshotsRetrieve information about the databaseThe STUFF FunctionFOR XML PATHCursorsJoinCommon Table ExpressionsMove and copy data around tablesDatesLimit Result SetRetrieve Information about your InstanceWith Ties Option VariablesJSON in Sql ServerWindow functionsPartitioningStored ProceduresGROUP BYGenerating a range of datesCOALESCESplit String function in Sql ServerINSERT INTOCREATE VIEWString FunctionsResource GovernorORDER BYWHILE loopSystem database - TempDbMigrationPrimary KeysMERGEFull-Text IndexingFOR JSONSELECT statementDBMAILIndexQueries with JSON dataStoring JSON in SQL tablesOPENJSONRanking FunctionsTriggerConverting data typesNULLsTransaction isolation levelsAdvanced optionsIF...ELSETRY/CATCHData TypesUser Defined Table TypesTable Valued ParametersIn-Memory OLTP (Hekaton)Temporal TablesInsertSequencesSCOPE_IDENTITY()ViewsUse of TEMP TableScheduled Task or JobIsolation levels and lockingSorting/ordering rowsPrivileges or PermissionsForeign KeysSQLCMDFile Groupcross applyBasic DDL Operations in MS SQL ServerComputed ColumnsUNIONSubqueriesLast Inserted IdentityCLUSTERED COLUMNSTOREParsenameInstalling SQL Server on WindowsAggregate FunctionsQuerying results by pageSchemasBackup and Restore DatabaseTransaction handlingNatively compiled modules (Hekaton)Database permissionsSpatial DataDynamic SQLPaginationQuery HintsModify JSON textRow-level securityDynamic data maskingExport data in txt file by using SQLCMDEncryptionManaging Azure SQL DatabaseCommon Language Runtime IntegrationDelimiting special characters and reserved wordsCASE StatementDBCCBULK ImportQuery StoreService brokerAnalyzing a QueryMicrosoft SQL Server Management Studio Shortcut KeysPermissions and SecurityPHANTOM readFilestreamDrop KeywordString Aggregate functions in SQL ServerSQL Server Evolution through different versions (2000 - 2016)SQL Server Management Studio (SSMS)Logical FunctionsDynamic SQL PivotAlias Names in Sql Serverbcp (bulk copy program) Utility

Scheduled Task or Job

Other topics

Create a scheduled Job

Create a Job

  • To add a job first we have to use a stored procedure named sp_add_job

    USE msdb ;  
    GO  
    EXEC dbo.sp_add_job  
    @job_name = N'Weekly Job' ;  -- the job name
    
  • Then we have to add a job step using a stored procedure named sp_add_jobStep

    EXEC sp_add_jobstep  
    @job_name = N'Weekly Job',  -- Job name to add a step
    @step_name = N'Set database to read only',  -- step name
    @subsystem = N'TSQL',  -- Step type
    @command = N'ALTER DATABASE SALES SET READ_ONLY',   -- Command
    @retry_attempts = 5,  --Number of attempts
    @retry_interval = 5 ; -- in minutes
    
  • Target the job to a server

    EXEC dbo.sp_add_jobserver  
    @job_name = N'Weekly Sales Data Backup',
    @server_name = 'MyPC\data;   -- Default is LOCAL
    GO
    

Create a schedule using SQL

To Create a schedule we have to use a system stored procedure called sp_add_schedule

USE msdb 
GO  

EXEC sp_add_schedule  
    @schedule_name = N'NightlyJobs' ,  -- specify the schedule name
    @freq_type = 4,   -- A value indicating when a job is to be executed (4) means Daily
    @freq_interval = 1,  -- The days that a job is executed and depends on the value of `freq_type`.
    @active_start_time = 010000 ;   -- The time on which execution of a job can begin
GO  

There are more parameters that can be used with sp_add_schedule you can read more about in the the link provided above.

Attaching schedule to a JOB

To attach a schedule to an SQL agent job you have to use a stored procedure called sp_attach_schedule

-- attaches the schedule to the job BackupDatabase  
EXEC sp_attach_schedule  
   @job_name = N'BackupDatabase',  -- The job name to attach with
   @schedule_name = N'NightlyJobs' ;  -- The schedule name
GO  

Contributors

Topic Id: 5329

Example Ids: 18958

This site is not affiliated with any of the contributors.