Using Blat and xp_cmdshell in SQL Server 2000

This has happened to me twice here at work:

I set up an automatic script to run regularly to do some processing for a department.  I create folders on the shared drive to export the processed files to.  Then the user in that department comes along and (accidentally or otherwise) deletes or renames one of those folders used for export in my script.  Then the script doesn't run anymore, and they come and ask me what's wrong.

Lesson learned: ALWAYS have your scripts check to verify that all necessary executables and paths are present, and have a way for the script to notify me if it is otherwise.

Today I spent most of the day coming up with some code to put in our custom payroll processing SQL stored procedure that checks to make sure the export directories are present, and creates them and notifies me if they aren't.

DECLARE @result1 int, @result2 int
EXEC master..xp_cmdshell 'if not exist "\oursvrsharePayrollExports for Payroll" echo Folder "sharePayrollExports for Payroll" could not be found.  Creating... >foldercheck1.txt',no_output
EXEC master..xp_cmdshell 'if not exist "\oursvrsharePayrollExcel Hours Report" echo Folder "sharePayrollExcel Hours Report" could not be found.  Creating... >foldercheck2.txt',no_output

/*
If these files exist then the folders couldn't be found, else these files won't exist and this command will fail and @result will be 1
(which is a good thing, it means the folders were located.)
*/
EXEC @result1 = master..xp_cmdshell 'TYPE foldercheck1.txt',no_output
EXEC @result2 = master..xp_cmdshell 'TYPE foldercheck2.txt',no_output

IF @result1 = 0  --Folder not found
BEGIN
    EXEC master..xp_cmdshell 'blat foldercheck1.txt -to it@k.com -ss -u blat@k.com -pw opensesame -q'
    EXEC master..xp_cmdshell 'mkdir "\oursvrsharePayrollExports for Payroll"',no_output
END

IF @result2 = 0  --Folder not found
BEGIN
    EXEC master..xp_cmdshell 'blat foldercheck2.txt -to it@k.com -ss -u blat@k.com -pw opensesame -q'
    EXEC master..xp_cmdshell 'mkdir "\oursvrsharePayrollExcel Hours Report"',no_output
END

EXEC master..xp_cmdshell 'DEL /Q foldercheck*.txt',no_output  --DEL does not return 1 if it cannot find the file to delete!

Of course, blat must be installed.  And I really should check that blat is present or else give an error.