September 22, 2008 at 8:51 am
I want add a activexscript or vbscript in sql agent jobs such that if file in a directory exists then excute second step , if not then end job successful.
September 22, 2008 at 9:02 am
Hi,
You don't necessarily need an activeX script in the Agent job. You can do it by using the xp_fileexists extended proc in T-SQL.
CREATE TABLE #exists (file_exist BIT, is_dir BIT, parent_dir BIT)
INSERT INTO #exists
execute master..XP_FILEEXIST '\\MachineName\directory\file.txt'
IF EXISTS (SELECT * FROM #exists WHERE file_exist = 1)
RAISERROR('File Exists', 10, 1) -- Non-fatal error as notification
ELSE
RAISERROR ('File Does Not Exist', 16, 1) -- Fatal error failing step
You can then have the step go to step X on pass and step Y on failure.
September 22, 2008 at 9:33 am
but I don't know the filename or you can say any fillename in directory.
I know only path upto directory.
if a file is in directory then execute second step.
please help.
September 22, 2008 at 9:41 am
You can use xp_cmdshell to run the DIR command.
DECLARE @dir VARCHAR(200)
DECLARE @cmd VARCHAR(205)
SELECT @dir = '\\machine\dir'
SELECT @cmd = 'DIR ' + @dir
CREATE TABLE #directoryFiles (lineID INT, line VARCHAR(2000))
INSERT INTO #directoryFiles(line)
EXEC master.dbo.xp_cmdshell @cmd
SELECT * FROM #directoryFiles
DROP TABLE #directoryFiles
This will list all files in a directory, you can then use SUBSTRING to parse out the filenames. Note that the lineID is useful for getting rid of unwanted header and footer lines returned by the DIR command.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply