use master
go

IF OBJECT_ID('sp_ABconvertuniqueid') IS NOT NULL
        DROP PROCEDURE dbo.sp_ABconvertuniqueid
GO

CREATE PROCEDURE sp_ABconvertuniqueid(@in varchar(40), @out varchar(40) OUTPUT)
AS
SELECT @out = substring(@in,7,2) + substring(@in,5,2) + substring(@in,3,2) + substring(@in,1,2) 
              + '-' + substring(@in,11,2) + substring(@in,9,2)
              + '-' + substring(@in,15,2) + substring(@in,13,2)
              + '-' + substring(@in,17,4) 
              + '-' + substring(@in,21,12) 
GO

IF OBJECT_ID('sp_ABhelp_revmaintplans') IS NOT NULL
        DROP PROCEDURE dbo.sp_ABhelp_revmaintplans
GO

CREATE PROCEDURE sp_ABhelp_revmaintplans

/* Name: sp_ABhelp_revmaintplans
 *
 * Filename: Personal/Source/sp_ABhelp_revmaintplans.sql
 *
 * Purpose:  Reverse-engineer existing SQL Server 7 Maintenance Plans.  This will script out
 *           existing jobs that are part of a maintenance plan, then add SQL to insert necessary
 *           data for the maintenance plan tables.  It seems to handle multi-server jobs OK also.
 *           
 * Output:   SQL which may be cut and pasted to the target server.
 *
 * Called by: N/A
 *
 * Written by:  Vince Iacoboni  vbi@writeme.com
 *
 * Modification history:
 *  01/10/2001  VRI  Created.
 *  07/20/2001  VRI  Create the jobs that are part of the plan also.
 *
 * Sample call:
 *  EXEC sp_ABhelp_revmaintplans 'User%'
 *
 */

@PlanName   varchar(60) = '',
@Server varchar(30) = null,
@UserName varchar(30) = null,
@PassWord varchar(30) = null

AS

DECLARE @object int
DECLARE @hr int
DECLARE @return varchar(200)
DECLARE @exec_str varchar(200)
DECLARE @J int
DECLARE @JobCount int
DECLARE @property varchar(255)
DECLARE @execstr varchar(255)
DECLARE @JobId varchar(60)
DECLARE @FileName varchar(200)

SET NOCOUNT ON

/* get the MSSQL7 directory since we should be able to write a file there */
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\Setup', 
                           'SQLPath', @param=@FileName OUTPUT

SELECT @FileName = @FileName + '\LOG\SCRIPT.TMP' 

SELECT @PlanName = rtrim(@PlanName) + '%',
       @J = 1

CREATE TABLE #Output (OutputLine varchar(1000) NULL DEFAULT ' ')

-- Get the jobs that are part of our plans
SELECT  plan_id, s.job_id 
  INTO  #Jobs
  FROM  msdb..sysjobsteps s, 
        msdb..sysdbmaintplans p 
  WHERE command LIKE '%-PlanId ' + rtrim(convert(varchar(40), p.plan_id)) + '%'
    AND p.plan_name LIKE @PlanName

-- Set the server to the local server
IF @server is NULL
  SELECT @server = @@servername
  
-- Connect to the SQL Server
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0 GOTO ERRORLABEL

IF (@UserName is NULL) AND (@PassWord is NULL)
  BEGIN
    -- Windows NT Authentication mode is used
    EXEC @hr = sp_OAMethod @object, 'Connect', NULL,  @server
    IF @hr <> 0 GOTO ERRORLABEL
  END
ELSE
IF (@UserName is NULL)
  BEGIN
    -- Set the username to the current user name
    SELECT @UserName = SYSTEM_USER
    EXEC @hr = sp_OAMethod @object,'Connect',NULL,@server,@UserName,@PassWord
    IF @hr <> 0 GOTO ERRORLABEL
  END
ELSE
IF (@PassWord is NULL)
  BEGIN
    EXEC @hr = sp_OAMethod @object, 'Connect', NULL,  @server, @UserName
    IF @hr <> 0 GOTO ERRORLABEL
  END
ELSE
  BEGIN
    EXEC @hr = sp_OAMethod @object,'Connect',NULL,@server,@UserName,@PassWord
    IF @hr <> 0 GOTO ERRORLABEL
  END

-- Verify the connection
EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF @hr <> 0 GOTO ERRORLABEL

-- Delete the script file if it exists
SELECT @execstr = 'CMD /C IF EXIST "' + @filename + '" DEL "' + @filename + '"'
EXEC master..xp_cmdshell @execstr

-- Find out how many jobs are in the server
EXEC @hr = sp_OAGetProperty @object, 'JobServer.Jobs.Count', @property OUTPUT
IF @hr <> 0 GOTO ERRORLABEL
SELECT @JobCount = convert(int, @property)

SELECT @J = 1

WHILE @J <= @JobCount
  BEGIN

    -- Get this job's id
    
    SELECT @execstr = 'Jobserver.Jobs(' + convert(varchar(3), @J) + ').JobId'
    EXEC @hr = sp_OAGetProperty @object, @execstr, @property OUTPUT
    IF @hr <> 0 GOTO ERRORLABEL

    EXEC sp_ABconvertuniqueid @property, @JobId OUTPUT

    IF @JobId IN (SELECT job_id FROM #Jobs)
      BEGIN

        -- Script out this job to our filename
        SELECT @execstr = 'Jobserver.Jobs(' + convert(varchar(3), @J) + ').Script(327, "' + @filename + '",3074)'
        EXEC @hr = sp_OAMethod @object, @execstr, @return OUT
        IF @hr <> 0 GOTO ERRORLABEL

      END

    SELECT @J = @J + 1
  END  

SELECT @execstr = 'CMD /C TYPE "' + @filename + '"'

INSERT #Output
  EXEC master..xp_cmdshell @execstr


UPDATE #Output
  SET OutputLine = DEFAULT
  WHERE OutputLine IS NULL

INSERT #Output
SELECT "insert msdb..sysdbmaintplans select '" 
       + convert(varchar(40), plan_id)
       + "', '"  + plan_name 
       + "', '" + convert(varchar(40), date_created, 101) 
       + "', '" + owner 
       + "', " + convert(varchar(10), max_history_rows) 
       + ", '" + remote_history_server 
       + "', " + convert(varchar(10), max_remote_history_rows)
       + ", " + isnull(convert(varchar(10), user_defined_1), 'NULL') 
       + ", " + isnull("'" + user_defined_2 + "'",'NULL') 
       + ", " + isnull('"' + convert(varchar(40), user_defined_3,101) + "'",'NULL') 
       + ", " + isnull("'" + convert(varchar(40), user_defined_4) + "'", 'NULL')
  FROM msdb..sysdbmaintplans
  WHERE plan_name LIKE @PlanName
    AND plan_id NOT LIKE '0000%'


INSERT #Output
SELECT "insert msdb..sysdbmaintplan_databases select '" 
       + convert(varchar(40), plan_id)
       + "', '"  + database_name + "'"
  FROM msdb..sysdbmaintplan_databases
  WHERE plan_id IN (SELECT plan_id 
                      FROM msdb..sysdbmaintplans 
                      WHERE plan_name LIKE @PlanName)

INSERT #Output
SELECT "DELETE msdb..sysdbmaintplan_jobs " 
     + "  FROM msdb..sysdbmaintplans p "
     + "  WHERE msdb..sysdbmaintplan_jobs.plan_id = p.plan_id"
     + "    AND p.plan_name LIKE '" + @PlanName + "'"

INSERT #Output
SELECT "INSERT sysdbmaintplan_jobs " 
     + "  SELECT DISTINCT plan_id, s.job_id " 
     + "  FROM msdb..sysjobsteps s, msdb..sysdbmaintplans p "
     + "  WHERE command like '%-PlanId ' + rtrim(convert(varchar(40), p.plan_id)) + '%'"
     + "    AND p.plan_name LIKE '" + @PlanName + "'"

INSERT #Output
SELECT "GO"

SELECT * from #Output
GOTO NOERROR

ERRORLABEL: 
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    GOTO NOERROR
END

NOERROR:
GO

GRANT EXECUTE ON dbo.sp_ABhelp_revmaintplans TO public
GO