Creation of an SQL job

[talon]

I have a task I need to create an SQL job, that will compare a file path listed in the database, and see if the path actually exists, and for the ones it does not see that dont exist to email the results to an operator ..

IS there a way? I need help.
Thanks

[navneet]

Use xp_cmdshell procedure to run 'dir' or any DOS command to check if file exists or not.
For syntax, look in BOL.

[John Jayaseelan]

Hi,

Activex script can be written for checking the file/directory existence.

Function ReportFileStatus(filespec)
Dim fso, msg
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(filespec)) Then
msg = filespec & " exists."
Else
msg = filespec & " doesn't exist."
End If
If (fso.FolderExists(fldr)) Then
msg = fldr & " exists."
Else
msg = fldr & " doesn't exist."
End If
ReportFileStatus = msg
End Function

John Jayaseelan

[talon]

Once I have the following I've had problems looping through and sending myself an email with the results.

IF OBJECT_ID('tempdb..#fileexist','u') IS NOT NULL
DROP TABLE #fileexist
CREATE TABLE #fileexist (fileExists tinyint, fileIsDir tinyint, parentDirExists tinyint)

DECLARE @fileName VARCHAR(100)
DECLARE @category VARCHAR(50)
DECLARE @subCat VARCHAR(50)
DECLARE @scanName VARCHAR(50)
DECLARE @msg VARCHAR(200)

SELECT @category = Drawingcategory,
@subCat = DrawingsubCategory,
@scanName = ScanName
FROM SCANDWG

WHERE
DrawingCategory = '100000 DWGS'
OR DrawingCategory = '200000 DWGS'
OR DrawingCategory = '300000 DWGS'
OR DrawingCategory = '400000 DWGS'
OR DrawingCategory = '500000 DWGS'
OR DrawingCategory = '600000 DWGS'
OR DrawingCategory = '700000 DWGS'

AND DrawingSubCategory = 'Assembly Drawings'
AND DrawingSubCategory = 'Component Drawings'



-- 1 FILE ONLY. NEED TO LOOP THIS AND PRODUCE A RESULT SET.
SET @fileName = 'C:\'-- + @category + '\' + @subCat + '\' + @scanName + '.TIF'

INSERT INTO #fileexist

EXEC master..xp_fileexist @fileName --,@DrawingCategory --@DrawingSubCategory @ScanName

-- a second file?
-- SET @fileName = 'C:\' + @category + '\' + @subCat + '\' + @scanName + '.TIF'
-- INSERT INTO #fileexist
-- EXEC master..xp_fileexist @fileName


SELECT * FROM #fileexist --uncomment to verify insert into temp table

-- IF (SELECT fileExists FROM #fileexist) = 0 --file does not exist
-- BEGIN
-- --!!change code below to match what you need to do when a file is not found
-- SET @msg = 'File=' + @fileName + ' not found.' --!!chg message as needed
-- --EXEC xp_sendmail , @message = @msg,
--exec master..xp_sendmail
--
-- @recipients = 'Someone@microsoft.com,
-- @subject = 'My File Report'
-- @width=120,
--@query = 'SELECT * FROM #fileexist',
-- @no_header = 'TRUE',
-- @no_output = 'FALSE',
-- @message = @msg,
-- @attach_results = 'TRUE'

--!!chg other params as needed
-- END --IF


I cant seem to get this to loop and produce a list of all the files in the database. Rather in this example above it only shows 2 results...
Thats really all im having a problem with.. help me ;-)