Check if files exist Stored Proc

15 Sep 2005 2:21 PM

Test Test

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.developersdex.com ***

15 Sep 2005 2:25 PM

Aaron Bertrand [SQL Server MVP]

Look up xp_fileexist in Books Online.

Show quote

"Test Test" <farooqhs_2***@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.developersdex.com ***

15 Sep 2005 2:31 PM

Aaron Bertrand [SQL Server MVP]

Whoops, never mind! I forgot this proc is not documented / supported.

Show quote

"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.

15 Sep 2005 2:36 PM

JT

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/default.asp?url=/library/en-us/script56/html/sgProgrammingFileSystemObject.asp

Show quote

"Test Test" <farooqhs_2***@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.developersdex.com ***

15 Sep 2005 2:37 PM

Jens

A particular file can be checked with:

EXEC master..xp_fileexist 'c:\boot.ini'

HTH, Jens Suessmeyer.

15 Sep 2005 3:03 PM

Test Test

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.developersdex.com ***

15 Sep 2005 3:55 PM

Aaron Bertrand [SQL Server MVP]

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

Show quote

"Test Test" <farooqhs_2***@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.developersdex.com ***

15 Sep 2005 4:57 PM

Test Test

Aaron! Thanx a lot!!!. It works like a champ!!!

*** Sent via Developersdex http://www.developersdex.com ***

15 Sep 2005 4:13 PM

ML

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