setLogDump

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.setLogDump') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.setLogDump
GO

CREATE PROCEDURE setLogDump
    @dbName VARCHAR(30) = ' ',
    @dumpPath VARCHAR(50) = ' ',            -- fully qualified dump path
    @loadPath VARCHAR(50) = ' ',            -- fully qualified load path 
    @name VARCHAR(30) = ' ' OUTPUT,            
    @debug VARCHAR(5) = 'false'
AS
DECLARE 
    @CMD VARCHAR(255),                      -- reusable command holder 
    @CMDstatus INT,                         -- return status of command prompt  
    @localCMDstatus INT,                    -- return status of command prompt  
    @dumpDevice VARCHAR(80),                -- fully qualified dump device
    @dbDumpName VARCHAR(20),                -- database dump file name
    @dbDumpTemp VARCHAR(20),                -- database dump file name
    @loadDevice VARCHAR(80),                -- fully qualified load device
    @currentDate DATETIME,                  -- used to make dump file name
    @dumpSeq TINYINT,                       -- used to make dump file name 
    @myName VARCHAR(30)                     -- this procedures name  
/* find a dump file name that is not in use. 
   Look in the dump and load folders.
   This will support up to 253 log dump files
   only the first 99 will be 8.3 file name */
SET NOCOUNT ON

IF (@dbName = ' ') OR (@dumpPath = ' ') OR (@loadPath = ' ') RETURN 1

SELECT @myName = 'admin.setLogDump'

SELECT @dbDumpName =  SUBSTRING(@dbName,1,4) +"dump.DAT" 
SELECT @dbDumpTemp =  SUBSTRING(@dbName,1,4) +"dump.TMP" 
SELECT @currentDate = (SELECT getdate())
SELECT @CMDstatus = 0
SELECT @dumpSeq = 1
WHILE (@CMDstatus = 0) AND (@dumpSeq < 254) -- success
  BEGIN -- look in the dump directory 
    SELECT @name = CONVERT(varchar(2), datepart(month,@currentDate)) + 
                   CONVERT(varchar(2), datepart(day,@currentDate)) + 
                   SUBSTRING(CONVERT(char(4), datepart(year,@currentDate)),3,2) + 
                   CONVERT(varchar(3), @dumpSeq) + ".DAT" 
    SELECT @dumpDevice = @dumpPath + '\' + @name
    SELECT @CMD = 'dir ' + @dumpdevice
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug 
    IF (@CMDstatus <> 0) -- did not find it
      BEGIN -- look in load folder
        SELECT @loadDevice = @loadPath + '\' + @name
        SELECT @CMD = 'dir ' + @loaddevice
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug 
        IF (@CMDstatus = 0) -- found it
          SELECT @dumpSeq = @dumpSeq + 1 -- try next name
        ELSE
          BEGIN
            IF (@dumpseq = 1) -- remove previous log dumps
              BEGIN 
                SELECT @CMD = 'dir ' + @dumpPath + '\' + @dbDumpName

                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'ren ' + @dumpPath + '\' + @dbDumpName + ' ' + @dbDumpTemp
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
                SELECT @CMD = 'dir ' + @dumpPath + '\*.DAT'
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'del ' + @dumpPath + '\*.DAT' 
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 

                SELECT @CMD = 'dir ' + @dumpPath + '\' + @dbDumpTemp 
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'ren ' + @dumpPath + '\' + @dbDumpTemp + ' ' + @dbDumpName
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 
                SELECT @CMD = 'dir ' + @loadPath + '\' + @dbDumpName
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'ren ' + @loadPath + '\' + @dbDumpName + ' ' + @dbDumpTemp
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 

                SELECT @CMD = 'dir ' + @loadPath + '\*.DAT'
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'del ' + @loadPath + '\*.DAT' 
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 

                SELECT @CMD = 'dir ' + @loadPath + '\' + @dbDumpTemp
                EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                IF (@localCMDstatus = 0) -- found
                  BEGIN
                    SELECT @CMD = 'ren ' + @loadPath + '\' + @dbDumpTemp + ' ' + @dbDumpName
                    EXEC @localCMDstatus = dispatch @CMD, @myName, @debug 
                    IF (@localCMDstatus <> 0) RETURN @localCMDstatus
                  END 

              END                              
          END
      END  
    ELSE -- found this file in the dump archive folder
        SELECT @dumpSeq = @dumpSeq + 1 -- try next name      
    IF @dumpSeq > 254
      BEGIN
        SELECT @CMD = "Unable to create a unique log dump name. Cannot continue. (" 
                      + @myName + ")"
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus = 0) RETURN @CMDstatus
        RETURN 99
      END
  END -- get the next dump file name
RETURN

GO