Technical Article

Perform an action when a file exisits


Script to check whether a file exists with todays date and performs an action based on whether the file exisits or not. We use this to check whether backups have completed from another SQL Srver and if they have completed we restoe on a server used for management information

        @Convdate varchar(8),
@NewDate varchar(13)

SELECT @counter = 0
-- Adjust counter for number of checks, so if a job runs late we can check for when the file actually appears
While (@Counter < 10)

-- convert todays date to text for date comparison
select @Convdate = convert (char(8),getdate(),112)

--Create temporary table
create table #fileexists ( 
Altname varchar(32),
Size varchar(12),
Creatdt varchar(13),
Creatti varchar(13),
LstWridt varchar(17),
LstWriti varchar(17),
LstAccdt varchar(18),
LstAccti varchar(18),
Attrib varchar(11)

-- Insert into the temporary table
Insert into #fileexists exec master..xp_getfiledetails 'e:\mssql7\mi\Backups_Completed.txt'
--change file details as required

select * from #fileexists

--Queries the temporary table to see if the file exists for todays date
select @NewDate = (select LstWridt from #fileexists)
-- print out file date
print @newdate
-- print out todays date
print @Convdate

    If exists (select LstWridt from #fileexists FE    
    where FE.LstWridt = @Convdate)

--Create completed file If job Completes File Exists
Exec master..xp_cmdshell 'echo > e:\Mssql7\MI\MI_Process_Started.txt'
Select @Counter = 10
-- Must change the above parameters to match server directories and filenames, to create a flag file.
Print 'File Does Exists and Running job'
Else Begin
-- If at this point file does not exist yet so.... 
Print 'File Does Not Exists'
-- Build in delay before next check
WAITFOR DELAY '00:20:00'
        -- above waits 20 minutes
Print @counter
-- Drop the TempDB
DROP TABLE #fileexists
Select @Counter = @Counter + 1