USE master
go

IF OBJECT_ID('sp_ABhelp_revtasks') IS NOT NULL
        DROP PROCEDURE sp_ABhelp_revtasks
GO

CREATE PROCEDURE sp_ABhelp_revtasks    

/*  Name:       sp_ABhelp_revtasks

    Purpose:    Reverse engineer SQL Executive scheduled tasks so that they can be backed up 
                or transferred to another server.

    Source:     Obtained from SQL Server mailing list (www.swynk.com)

    History:
      01/29/98  VRI   Created procedure from scarfed code.

*/
AS

SELECT "msdb..sp_addtask "+ CHAR(39) + name + CHAR(39) + "," +
        subsystem + ",@@SERVERNAME," +
       (SELECT name FROM master..syslogins
        WHERE suid = ownerloginid) + "," +
       databasename + "," +
       CONVERT(VARCHAR(10),enabled) + "," +
       CONVERT(VARCHAR(10),freqtype) + "," +
       CONVERT(VARCHAR(10),freqinterval) + "," +
       CONVERT(VARCHAR(10),freqsubtype) + "," +
       CONVERT(VARCHAR(10),freqsubinterval) + "," +
       CONVERT(VARCHAR(10),freqrelativeinterval)+ "," +
       CONVERT(VARCHAR(10),freqrecurrencefactor) + "," +
       CONVERT(VARCHAR(10),activestartdate) + "," +
       CONVERT(VARCHAR(10),activeenddate) + "," +
       CONVERT(VARCHAR(10),activestarttimeofday) + "," +
       CONVERT(VARCHAR(10),activeendtimeofday) + "," +
       CONVERT(VARCHAR(10),nextrundate) + "," +
       CONVERT(VARCHAR(10),nextruntime) + "," +
       CONVERT(VARCHAR(10),runpriority) + "," + CHAR(39) +
       (SELECT name FROM msdb..sysoperators
        WHERE id = emailoperatorid) + CHAR(39) + "," +
       CONVERT(VARCHAR(10),retryattempts)  + "," +
       CONVERT(VARCHAR(10),retrydelay) + "," +
       CHAR(39) + command + CHAR(39) + "," +
       CONVERT(VARCHAR(10),loghistcompletionlevel) + "," +
       CONVERT(VARCHAR(10),emailcompletionlevel) + "," +
       CHAR(39) + description + CHAR(39)
  FROM msdb..systasks
GO

GRANT EXECUTE ON sp_ABhelp_revtasks TO PUBLIC
GO