poll filesystem to wait for file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

poll filesystem to wait for file

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-10-04 : 12:11:05

[code]/*
Stored procedure that polls a specific folder for a given file
once a minute a given amount of time. If/when the file is found
the filename is prefixed with the current date/time and is left
in the same folder. If the file isn't found after the predefined
number of minutes the procedure throws an error. Executed like this:

EXEC database.dbo.WaitForFile
@FilePath = 'c:\temp\',
@FileName = 'file.txt',
@WaitMinutes = 60 --> Poll every minute for 60 minutes

This procedure depends on access to the system stored procedures
xp_fileExist and xp_cmdshell and proper permissions on the disks
where the files reside.

Procedure written by:
Henning Frettem (aka Lumbago, sqlteam.com) 2007-10-03
*/

CREATE PROCEDURE dbo.WaitForFile (
@FilePath VARCHAR(200),
@FileName VARCHAR(200),
@WaitMinutes INT
)
AS

DECLARE
@FilePathFull VARCHAR(200),
@FileExists INT,
@Counter INT,
@ErrMsg VARCHAR(400),
@cmd VARCHAR(400)

SET @FilePathFull = @FilePath + @FileName
SET @FileExists = 0
SET @Counter = 0

EXEC master.dbo.xp_fileExist @FilePathFull, @FileExists OUTPUT

WHILE @FileExists = 0 AND @Counter < @WaitMinutes
BEGIN
SET @Counter = @Counter + 1
WAITFOR delay '00:01:00'

EXEC master.dbo.xp_fileExist @FilePathFull, @FileExists OUTPUT
END

IF @FileExists = 0
BEGIN
SET @ErrMsg = 'FileWait for ''' + @FilePathFull +
''' timed out after waiting for ' + CAST(@Counter AS VARCHAR(20)) +
' minutes'
RAISERROR (@ErrMsg, 16, 1)
RETURN
END
ELSE IF @FileExists = 1
BEGIN
SET @cmd = 'rename ' + @FilePathFull + ' ' +
CONVERT(VARCHAR(20), GETDATE(), 112) + '_' +
REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '') + '_' +
@FileName
EXEC master.dbo.xp_cmdshell @cmd, no_output
END
[/code]

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources