Procedures - usp_fs_wmi_defrag_drives_winxp

CREATE PROCEDURE [dbo].[usp_fs_wmi_defrag_drives_winxp]
  @DriveLetter   char(1)=NULL
, @ServerName   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 @cmd = ''
SET @FileSystemTempDirectory = [dbo].[udf_AppDefault]('FileSystemTempDirectory', 'System',@@SERVERNAME, 'DBATools')
SET @ServerName  = ISNULL(@ServerName , @@SERVERNAME)
SET @DatabaseName = db_name()
SET @FullPathLogFileName = ISNULL(@FullPathLogFileName,
  [dbo].[udf_AppendIfNotFound](@FileSystemTempDirectory , '\')
 + [dbo].[udf_getTempName]('usp_fs_wmi_defrag_drives_winxp.log', GetDate(), NULL, 1)
 )
---------------------------------------------------------------------------
-- Setup command string
---------------------------------------------------------------------------
SET @cmd = @cmd  + '
Function isDefragRunning()
    strComputer = "."
    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

SUB DefragDrives(DriveLetter)
''-------------------------------------------------------------------------------------
'' Declare constants
''-------------------------------------------------------------------------------------
Const ForWriting = 2
Const ForAppending = 8
Dim fso, d, dc, FullPathLogFileName, sTime
FullPathLogFileName = "' + @FullPathLogFileName + '"
''-------------------------------------------------------------------------------------
''
''-------------------------------------------------------------------------------------
On Error Goto 0
''-------------------------------------------------------------------------------------
'' Get objects
''-------------------------------------------------------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
set objLogFile = objFSO.OpenTextFile(FullPathLogFileName, ForWriting, True)
set wshell = createobject("wscript.shell")
if err.number > 0 then msgbox err.description
''-------------------------------------------------------------------------------------
'' Check for defrag already running
''-------------------------------------------------------------------------------------
if isDefragRunning() <> 0 then
 objLogFile.WriteLine "Unable to run defrag.  It is already running."
    objLogFile.WriteLine ""
 objLogFile.close
 wscript.quit
end if
''-------------------------------------------------------------------------------------
'' Defrag drives
''-------------------------------------------------------------------------------------
objLogFile.WriteLine "=====================Begging Defrag================================="
''-------------------------------------------------------------------------------------
'' Init variable
''-------------------------------------------------------------------------------------
strComputer = "."
Set dc = objFSO.Drives
For Each d in dc
   If d.DriveType = 2 Then
      objLogFile.WriteLine "defrag " & d & " -f "
   sTime = "Start Time:" & Now()
      objLogFile.WriteLine sTime
      Return = wShell.Run("defrag " & d & " -f " , 1, TRUE)
   sTime = "End Time:" & Now()
   objLogFile.WriteLine sTime
   End If
Next
objLogFile.close
''-------------------------------------------------------------------------------------
'' Destroy Objects and exit
''-------------------------------------------------------------------------------------
set wShell    = nothing
set objLogFile  = nothing
Set objFSO   = nothing
Set dc    = nothing
END SUB
'
SET @cmd = @cmd  + 'Call DefragDrives(' + ISNULL(CHAR(34) + @DriveLetter + CHAR(34), 'NULL') + ') '
---------------------------------------------------------------------------
-- Execute command
---------------------------------------------------------------------------
--PRINT @cmd
SET @FileSystemTempDirectory =
  [dbo].[udf_AppendIfNotFound](@FileSystemTempDirectory , '\')
 + 'usp_fs_wmi_defrag_drives_winxp.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 ' + @FileSystemTempDirectory
 PRINT @FileSystemTempDirectory
 EXEC @retval     = [dbo].[usp_srvr_xp_cmdshell]
    @cmd      = @FileSystemTempDirectory
  , @ReturnData    = 1
  , @Execute0Display1Both2 = 2
END
RETURN @retval
END