XP_CMDSHELL from DOS Prompt and SQL Mail!

[roja]

I have a problem like this.

I have a Folder on our NT4.0 server gets filled with different files with extensions(For ex. .bak, .new, .old, etc). These files with those extensions gets build up on a daily basis.

What I am looking for is,
a)I would like to do a count on those files with those extenstion
b)Send an email to the user and their boss if I have letz say more than 15 files of .bak or 15 files of .new files in that folder.
c) Also like to schedule this program to to run every day.

We have SQL Server6.5. Can some one assist me in coding this problem. Thanks
for your time. Roja96@hotmail.com

[Gregory]

something like this:

set nocount on
declare @cmd varchar(255)
,@directory varchar(255)

/* get list of files in directory */
select @directory = 'c: emp'
select @cmd = 'dir ' + @directory + ' /b'

/* insert into list of files into temp working table */
create table #tmpFileList (FileName varchar(255) not null, FileExtension varchar(255) null)

insert into #tmpFileList
(FileName)
exec master..xp_cmdshell @cmd


if @@rowcount =0
print 'no files today!'
else
begin
declare @FileName varchar(255)
,@FileExtension varchar(255)
,@pos int
,@temp varchar(255)

declare files_cursor cursor for
select FileName from #tmpFileList

open files_cursor

fetch next from files_cursor into @FileName
while (@@fetch_status=0)
begin
/* get file extension */
select @temp=@FileName
select @pos = charindex('.', @temp)
if @pos =0
select @temp = null
else
begin
while @pos<>0
begin
select @temp=substring(@temp, @pos+1, len(@temp))
select @pos = charindex(&#39;.&#39;, @temp)
end
end

update #tmpFileList set FileExtension = @temp
where current of files_cursor

fetch next from files_cursor into @FileName
end
close files_cursor
deallocate files_cursor

print &#39;list of files in directory:&#39;
print &#39;---------------------------&#39;
select FileExtension
,FileName
from #tmpFileList

print &#39;file groups:&#39;
print &#39;---------------------------&#39;
select FileExtension, count(*) FileCount from #tmpFileList
group by FileExtension
end

drop table #tmpFileList
set nocount off


let me know if you need anything else

------------
roja at 12/3/99 8:14:38 PM

I have a problem like this.

I have a Folder on our NT4.0 server gets filled with different files with extensions(For ex. .bak, .new, .old, etc). These files with those extensions gets build up on a daily basis.

What I am looking for is,
a)I would like to do a count on those files with those extenstion
b)Send an email to the user and their boss if I have letz say more than 15 files of .bak or 15 files of .new files in that folder.
c) Also like to schedule this program to to run every day.

We have SQL Server6.5. Can some one assist me in coding this problem. Thanks
for your time. Roja96@hotmail.com

[Gregory]

you can put that script into a stored proc in the master (or user) database on your SQL Server:

create procedure sp_FileInfoNotify
( @directory varchar(255)
,@recipient_list varchar(255)
)
as
set nocount on
declare @cmd varchar(255)
/* get list of files in directory */
select @directory = &#39;c: emp&#39;
select @cmd = &#39;dir &#39; + @directory + &#39; /b&#39;

/* insert into list of files into temp working table */
create table #tmpFileList (FileName varchar(255) not null, FileExtension varchar(255) null)

insert into #tmpFileList
(FileName)
exec master..xp_cmdshell @cmd


if @@rowcount =0
print &#39;no files today!&#39;
else
begin
declare @FileName varchar(255)
,@FileExtension varchar(255)
,@pos int
,@temp varchar(255)

declare files_cursor cursor for
select FileName from #tmpFileList

open files_cursor

fetch next from files_cursor into @FileName
while (@@fetch_status=0)
begin
/* get file extension */
select @temp=@FileName
select @pos = charindex(&#39;.&#39;, @temp)
if @pos =0
select @temp = null
else
begin
while @pos<>0
begin
select @temp=substring(@temp, @pos+1, len(@temp))
select @pos = charindex(&#39;.&#39;, @temp)
end
end

update #tmpFileList set FileExtension = @temp
where current of files_cursor

fetch next from files_cursor into @FileName
end
close files_cursor
deallocate files_cursor

print &#39;list of files in directory:&#39;
print &#39;---------------------------&#39;
select FileExtension
,FileName
from #tmpFileList

print &#39;file groups:&#39;
print &#39;---------------------------&#39;
select FileExtension, count(*) FileCount from #tmpFileList
group by FileExtension
end

/* ---------------------------------------- */
/* notify user(s) */
exec master.dbo.xp_sendmail @recipient_list,
@subject = &#39;File Information&#39;,
@message = &#39;tooooo many files!&#39;

drop table #tmpFileList
set nocount off
go

for information on how to use xp_sendmail stored proc - look in the SQL Server Books Online

to schedule this proc to run very day - you&#39;ll need to create a task -->
in SQL Server Enterprise Manager, under SQL Executive - Open &#39;Manage Scheuled Tasks&#39; --> click on &#39;New Task&#39; --> under type select &#39;TSQL&#39; --> in command box enter sp_FileInfoNotify &#39;<you folder name>&#39;, &#39;<e-mail separated by ;>&#39;

------------
Gregory at 12/9/99 10:25:51 AM

something like this:

set nocount on
declare @cmd varchar(255)
,@directory varchar(255)

/* get list of files in directory */
select @directory = &#39;c: emp&#39;
select @cmd = &#39;dir &#39; + @directory + &#39; /b&#39;

/* insert into list of files into temp working table */
create table #tmpFileList (FileName varchar(255) not null, FileExtension varchar(255) null)

insert into #tmpFileList
(FileName)
exec master..xp_cmdshell @cmd


if @@rowcount =0
print &#39;no files today!&#39;
else
begin
declare @FileName varchar(255)
,@FileExtension varchar(255)
,@pos int
,@temp varchar(255)

declare files_cursor cursor for
select FileName from #tmpFileList

open files_cursor

fetch next from files_cursor into @FileName
while (@@fetch_status=0)
begin
/* get file extension */
select @temp=@FileName
select @pos = charindex(&#39;.&#39;, @temp)
if @pos =0
select @temp = null
else
begin
while @pos<>0
begin
select @temp=substring(@temp, @pos+1, len(@temp))
select @pos = charindex(&#39;.&#39;, @temp)
end
end

update #tmpFileList set FileExtension = @temp
where current of files_cursor

fetch next from files_cursor into @FileName
end
close files_cursor
deallocate files_cursor

print &#39;list of files in directory:&#39;
print &#39;---------------------------&#39;
select FileExtension
,FileName
from #tmpFileList

print &#39;file groups:&#39;
print &#39;---------------------------&#39;
select FileExtension, count(*) FileCount from #tmpFileList
group by FileExtension
end

drop table #tmpFileList
set nocount off


let me know if you need anything else

------------
roja at 12/3/99 8:14:38 PM

I have a problem like this.

I have a Folder on our NT4.0 server gets filled with different files with extensions(For ex. .bak, .new, .old, etc). These files with those extensions gets build up on a daily basis.

What I am looking for is,
a)I would like to do a count on those files with those extenstion
b)Send an email to the user and their boss if I have letz say more than 15 files of .bak or 15 files of .new files in that folder.
c) Also like to schedule this program to to run every day.

We have SQL Server6.5. Can some one assist me in coding this problem. Thanks
for your time. Roja96@hotmail.com