• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

How to check if a file exists for evry 10 min for an hour

I have to import a txt file to a table . I have to check for every 10 min for an hour if the file  exist and if the
file exists then I have to import the file . Now I am inserting all the files in a folder(C:\) to a table and checking
if a  file exists . But if the file does not exists how can check every 10 min for an hour (I dont want this through SSIS
). I want a sql for this

Thanks
0
vijay11
Asked:
vijay11
  • 4
  • 2
  • 2
  • +2
1 Solution
 
Cedric Obinna A.Full Stack DeveloperCommented:
Can we see the code you use to check if the file exists?
0
 
elimesikaCommented:
HI

use

1) for checking if file exists http://www.mssqltips.com/tip.asp?tip=1272 
2)  crate a job in sql server that runs 1) every 10 min

if you use sql express , use this to define your job
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express
0
 
radcaesarCommented:
Use xp_cmdshell / xp_fileexists to check the file

Make it as an SP/bat file

Schedule it to run every 10 mins.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
vijay11Author Commented:

select @loc = 'c:'
select @cmd = master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @loc + '\*.*' + char(39)
SELECT @CMD2 = 'INSERT INTO a' + char(13) +
'EXEC ' + @CMD1

exec (@cmd)
and all the files that exists in the c: path will insert into temp table .Now what I want is for every 10 min for an hour I have to check if the file exists . I have to do this inside a proc . I cannot run this as a batch.
0
 
radcaesarCommented:
What is the problem?

Write your logic inside a procedure and schedule that procedure to run for every 10 mins.
0
 
vijay11Author Commented:
I got this solved my self  by using while  and delay function
0
 
Anthony PerkinsCommented:
>>I got this solved my self  by using while  and delay function <<
Please post your solution.
0
 
vijay11Author Commented:
Here is the solution

select @exist_id = 1,@exists = 0

while @exist_id <=6 and @exists=0
begin

-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = ''

-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @file_location + '\abc\*.*' + char(39)

-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' + @CMD1

if @exist_id <>1
begin
waitfor delay '00:10:00'
end
-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)

-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList
WHERE COL1 IS NULL

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Volume%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%<DIR>%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%bytes%'

-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)
--SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
select getdate(),
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList



if exists(select 1
          from #ParsedFileList
          where FileName1 like @file)--@file is the file that I am checking if exists in the folder
select @exists = 1
else
select @exists = 0
set @exist_id = @exist_id + 1
end

Here I am Inserting all the files in a particular folder to a temp table and from that table I am checking if the file exists (File with a particular name and extension )  
0
 
Anthony PerkinsCommented:
To whoever is reading this in future, the script posted is useless without the DECLARE for the following local variables:
@exist_id, @exists, @CMD1, @CMD2, @file_location, @File

And the CREATE TABLE for the following temporary tables:
#OriginalFileList
#ParsedFileList
0
 
vijay11Author Commented:
I got this Question solved my self
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now