March 20, 2008 at 10:49 am
I want to set up scheduler to check whether file is created monthly. If no file is created, I will send the creator email to create file . I do not need to import the data at all.
I like to use the scheduler(job) in sql server.
How can I do that ? Thx.
March 26, 2008 at 9:34 pm
If the filename is static and only the date is changing example : Lester_20080327.xls then you cna use xp_cmdshell to locate the filename in a specified location then use xp_sendmail or xp_smtp_sendmail to send mail to the user confirming to create the file
"-=Still Learning=-"
Lester Policarpio
March 27, 2008 at 12:34 pm
I googled xp_cmdshell to get file creation time did not come out anything. Will you please post some sample ?
Thx.
March 27, 2008 at 9:41 pm
Example :
exec master..xp_cmdshell 'dir /od C:\*.xls'
This will search all file with xls(excel) extension. you can insert the output to a table
example :
insert into tablw_name
exec master..xp_cmdshell 'dir /od C:\*.xls'
From here you can query the filename and the date created of the specific filename your are looking
"-=Still Learning=-"
Lester Policarpio
March 28, 2008 at 11:15 am
I do not know no match whether I have xls file or not.
I did this in query analyzer. the output is the same. Thx.
exec master..xp_cmdshell 'dir /od C:\*.xls'
output as :
Volume in drive C is Local Disk
Volume Serial Number is 9C07-D278
NULL
Directory of C:NULL
File Not Found
NULL
Volume in drive C is Local Disk
Volume Serial Number is 9C07-D278
NULL
Directory of C:NULL
File Not Found
NULL
March 30, 2008 at 7:19 pm
Almost forgot some points to consider
1. Where will the created report be placed??
2. Does it have (the place where the report will be dump) a sql server install in it? do you have access in that pc/server?
3. what is the extension of the report file? In my example it is excel file in your case maybe its different you can also try this one (assuming the file resides in your Drive C)
exec master..xp_cmdshell 'dir /od C:\*.*'
This will output all the file in your C Drive
"-=Still Learning=-"
Lester Policarpio
March 30, 2008 at 8:59 pm
If you know the exact path and name of the file you are looking for then try xp_FileExist:
declare @FilePathName varchar(100)
declare @file_exists int
set @FilePathName = 'L:\temp\Test.txt'
EXECUTE master.dbo.xp_FileExist @FilePathName, @file_exists OUTPUT
if @file_exists = 1
begin
print 'The file does exist '
end
If you just want to check a directory for something like "any file made in the last 2 days" or "made today" you may be able to use OS command FORFILES. Redirect the output to a text file, bulk insert the text file into sql server, check the list. One tricky bit here is that if no files match your pattern then the text file will not be created.
-- since we append each file we want to start with a clean slate
exec master.dbo.xp_cmdshell 'del L:temp\DirListTmp.txt '
-- use this to find ALL files
exec master.dbo.xp_cmdshell 'forfiles /p L:\temp\xyz\ /m * /c "cmd /c echo @ISDIR0x09@FDATE0x09@FTIME0x09@FSIZE0x09@FILE >> L:\temp\DirListTmp.txt " '
-- use this to find only TODAYs files
-- exec master.dbo.xp_cmdshell 'forfiles /p L:temp\xyz\ /m *.* /D +0 /c "cmd /c echo @ISDIR0x09@FDATE0x09@FTIME0x09@FSIZE0x09@FILE >> L:temp\DirListTmp.txt " '
March 31, 2008 at 6:08 am
I have the right to exec the command. The reason I can't use master.dbo.xp_FileExist is that I need to know the file created date so that I will send out email if file created date is more than 30 days.
The file in the server .
I also tried to do this:
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\Personal' ,
@FileName = '*.txt' --'file.bin'
declare @cmd varchar(8000)
create table #a(s varchar(8000))
select @cmd = 'dir /B /S ' + @Path + @FileName
insert #a exec master..xp_cmdshell @cmd
delete from #a where s is null
select * from #a
drop table #a
Result is file not found which is not sure since I have text file in the personal folder.
Thx.
March 31, 2008 at 9:33 am
You are missing a slash in @Path. You have c:\Personal*.txt not c:\Personal\*.txt.
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\Personal\' , ---<<<right here
@FileName = '*.txt' --'file.bin'
March 31, 2008 at 9:57 am
Thx. but it did not get me the file created time.
March 31, 2008 at 9:59 am
that's because you threw the /B switch. /B means Bare--only filenames.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply