Check if files exist Stored Proc

Hi,
This stored proc code uses DOS Copy command and xp_cmdshell stored
procedure to copy files form one location to another. See below the
code. It is working fine!. What I need is to add a check if the files in
the Source location exist or not. If not, then send an email (using
xp_sendmail) to us saying "files do not exist". If yes, then start
copying the files. How do I do that?
Thanks for your help.
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
select @.source =
case @.@.servername
when 'A' then '\\server1\folder1\a*.*'
when 'B' then '\\server2\folder2\b*.*'
when 'C' then '\\server3\folder3\c*.*'
end,
@.destination =
case @.@.servername
when 'A' then '\\serverx\folderx'
when 'B' then '\\serverx\folderx'
when 'C' then '\\serverx\folderx'
end
-- copy only if the files in the source folder exist, otherwise send an
email for "files do not exixts".
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
GO
*** Sent via Developersdex http://www.examnotes.net ***Look up xp_fileexist in Books Online.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Whoops, never mind! I forgot this proc is not documented / supported.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwg8xEguFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Look up xp_fileexist in Books Online.|||A particular file can be checked with:
EXEC master..xp_fileexist 'c:\boot.ini'
HTH, Jens Suessmeyer.|||If this were implemented as a DTS package, you would perhaps find the file
system object more suitable for copying files, etc.
http://msdn.microsoft.com/library/d...ystemObject.asp
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks for the respone. The "xp_fileexist" works great if you know the
exact file name. In my case, I dont know the file name. All I need is to
capture the files start with "a" i.e. a*.*
So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
Any other ideas?
*** Sent via Developersdex http://www.examnotes.net ***|||CREATE TABLE #files
(
filename SYSNAME NULL
)
SET NOCOUNT ON
INSERT #files EXEC master..xp_cmdshell 'dir \\server1\folder1\a*.* /b'
SELECT COUNT(*) FROM #files WHERE filename IS NOT NULL
SELECT * FROM #files WHERE filename IS NOT NULL
DROP TABLE #files
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uvoSyaguFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks for the respone. The "xp_fileexist" works great if you know the
> exact file name. In my case, I dont know the file name. All I need is to
> capture the files start with "a" i.e. a*.*
> So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
> Any other ideas?
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If you're already planning on using xp_cmdshell, then try this:
declare @.cmd varchar(1024)
declare @.path varchar(1024)
create table #fs
(
fId int identity (1, 1) primary key
,fName varchar(1024)
)
set @.path = 'c:\Inetpub*.*'
set @.cmd = 'dir /b ' + @.path
insert #fs
(
fName
)
exec master.dbo.xp_cmdshell @.cmd
if (exists (
select fs.fId
from #fs fs
where (fs.fName is not null)
))
begin
print 'exists'
end
else
begin
print 'does not exist'
end
ML|||Aaron! Thanx a lot!!!. It works like a champ!!!
*** Sent via Developersdex http://www.examnotes.net ***