Procedures - usp_fs_wmi_defrag_drives_win2003

CREATE PROCEDURE [dbo].[usp_fs_wmi_defrag_drives_win2003]
  @DriveLetter   char(1)=NULL
, @MachineName   varchar(80)=NULL
, @FullPathLogFileName varchar(500)=NULL
, @Execute0Display1Both2 tinyint=0
AS
BEGIN
---------------------------------------------------------------------------
-- Define variables
---------------------------------------------------------------------------
DECLARE
  @cmd      varchar(8000)
, @SingleQuote    CHAR(1)
, @DoubleQuote    CHAR(1)
, @CarriageReturn   CHAR(1)
, @FileSystemTempDirectory varchar(500)
, @retval     integer
, @DatabaseName    varchar(80)
---------------------------------------------------------------------------
-- Initialize variables
---------------------------------------------------------------------------
SET @SingleQuote = CHAR(39)
SET @DoubleQuote = CHAR(34)
SET @CarriageReturn = CHAR(13)
SET @FileSystemTempDirectory = [dbo].[udf_AppDefault]('FileSystemTempDirectory', 'System',@@SERVERNAME, 'DBATools')
SET @MachineName   = ISNULL(@MachineName , '.')
SET @DatabaseName   = db_name()
SET @FullPathLogFileName = ISNULL(@FullPathLogFileName, [dbo].[udf_AppendIfNotFound](@FileSystemTempDirectory , '\') + 'usp_fs_wmi_defrag_drives_win2003.log')
---------------------------------------------------------------------------
-- Setup command string
---------------------------------------------------------------------------
SET @cmd = 'Dim sTime, strComputer 
Const ForWriting = 2
Const ForAppending = 8
Const FullPathLogFileName = '
SET @cmd = @cmd  + @DoubleQuote
SET @cmd = @cmd  + @FullPathLogFileName
SET @cmd = @cmd  + @DoubleQuote + ' ' + @CarriageReturn
SET @cmd = @cmd  + '
strComputer = "' + @MachineName + '"
Function isDefragRunning()
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colProcesses = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = ''Dfrgntfs.exe''")
    isDefragRunning = colProcesses.Count
    Set colProcesses = nothing
    Set objWMIService = nothing
End Function
Set objFSO = CreateObject("Scripting.FileSystemObject")
set objLogFile = objFSO.OpenTextFile(FullPathLogFileName,  ForAppending, True)
if err.number > 0 then msgbox err.description
if isDefragRunning() <> 0 then
 objLogFile.WriteLine "' + @MachineName + ':Unable to run defrag.  It is already running."
    objLogFile.WriteLine ""
 objLogFile.close
 wscript.quit
end if
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colVolumes = objWMIService.ExecQuery ("Select * from Win32_Volume WHERE DriveType=3 '
---------------------------------------------------------------------------
-- Set drive letter
---------------------------------------------------------------------------
IF @DriveLetter IS NOT NULL
BEGIN
 SET @cmd = @cmd  + 'AND Name = '
 SET @cmd = @cmd  + @SingleQuote 
 SET @cmd = @cmd  + @DriveLetter
 SET @cmd = @cmd  + ':\\' 
 SET @cmd = @cmd  + @SingleQuote 
END
---------------------------------------------------------------------------
-- Continue setting up command string
---------------------------------------------------------------------------
SET @cmd = @cmd  + '") '
SET @cmd = @cmd  + '
objLogFile.WriteLine "=====================' + @MachineName + ':Begging Defrag================================="
For Each objVolume in colVolumes
 objLogFile.WriteLine "Please wait while drive " & objVolume.Name & " is defragged."
 sTime = Now()
    objLogFile.WriteLine "Start Time:" & sTime
     errResult = objVolume.Defrag(Force=True)
    If errResult = 0 Then
        objLogFile.WriteLine "Drive " & objVolume.Name & " successfully defragged."
    Else
        objLogFile.WriteLine "Drive " & objVolume.Name & " could not be defragged."
        objLogFile.WriteLine "Error number " & errResult & " occurred."
        Err.Clear
    End If
 sTime = Now()
    objLogFile.WriteLine "End Time:" & sTime
Next
objLogFile.close
Set colProcesses = nothing
Set objWMIService = nothing
'
---------------------------------------------------------------------------
-- Execute command
---------------------------------------------------------------------------
--PRINT @cmd
SET @FileSystemTempDirectory =
  [dbo].[udf_AppendIfNotFound](@FileSystemTempDirectory , '\')
 + 'usp_fs_wmi_defrag_drives_win2003.vbs'
-- + [dbo].[udf_getTempName]('usp_fs_wmi_defrag_drives.vbs', GetDate(), NULL, 1)
EXEC @retval  = [dbo].[usp_fs_CreateTextFile]
  @FullPathFileNameDestination  = @FileSystemTempDirectory
, @strTextToWriteToFile        = @cmd
, @boolOverwriteFile           = 0
IF @retval = 0
BEGIN
 SET @FileSystemTempDirectory = 'cmd.exe /C cscript.exe ' + @FileSystemTempDirectory
 PRINT @FileSystemTempDirectory
 EXEC @retval     = [dbo].[usp_srvr_xp_cmdshell]
    @cmd      = @FileSystemTempDirectory
  , @ReturnData    = 1
  , @Execute0Display1Both2 = 2
END
RETURN @retval
---------------------------------------------------------------------------
-- This procedure builds a file to defrag drives
-- A is currently needed
---------------------------------------------------------------------------
--EXEC usp_fs_wmi_defrag_drives @DriveLetter= 'C'
--EXEC usp_fs_wmi_defrag_drives @Execute0Display1Both2 = 2
--EXEC [dbo].[usp_fs_wmi_defrag_drives_win2003] @MachineName='DAMSDEV01'
--EXEC [dbo].[usp_fs_wmi_defrag_drives_win2003] @MachineName='DAMSDEV02'
--EXEC [dbo].[usp_fs_wmi_defrag_drives_win2003] @MachineName='DAMSDEV03'
END