if exists

Question

if exists

Timothy Moore

SSC Veteran

Points: 264
More actions
April 19, 2007 at 11:54 am

#195414

i am trying to check for existing files at a specific path, and then
if exists, xcopy the file from one server to another. here is my syntax:

declare
@Path varchar(128),
@FileName
varchar(128)
select @Path = '\\server\share$\',
@FileName
= '*.txt.gz'
declare
@cmd varchar(1000)
create table #Traffic(s varchar(1000))
select @cmd = 'dir /B ' + @Path + @FileName
insert #Traffic exec master..xp_cmdshell @cmd
if exists (exec master...xp_cmdshell 'XCOPY \\server\share$\*.txt.gz \\server\drive$\folder')
print 'files copied successfully'
else
print 'no files exists'
drop table #Traffic

however, it keeps returning a syntax error near the work 'exec and ')'

anyone help? can i do a string in a if exists statement?

thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

This shows several different ways to check for the existence of a file. Note that the last one (xp_fileexists) is undocumented, and therefore unsupported. That's not to say that we don't use it (I do), as we use undocumented stuff all of the time (see sp_MSForEachTable for an example).

Also, you have 3 dots between master and xp_cmdshell, when it should be 2.

Timothy Moore SSC Veteran Points: 264 More actions · Answer 2

declare

@Path varchar(128),

@FileName

varchar(128)

select @Path = '\\waprdaprpt03\OZ$\',

@FileName

= '*.txt.gz'

declare

@i int

declare

@File varchar(1000)

select @File = @Path + @FileName

exec master..xp_fileexists @File, @i out

if @i = 1

exec master..xp_cmdshell 'XCOPY \\server\share$\*.txt.gz \\server\drive$\directory'

then

print 'files copied successfully'

else

print 'no files exists'

Is this how it should look? still doesnt like syntax near 'then'

Christi Wilson Ten Centuries Points: 1030 More actions · Answer 3

if @i = 1

BEGIN

exec master..xp_cmdshell 'XCOPY \\server\share$\*.txt.gz \\server\drive$\directory'

Print 'files copied successfully'

END

else

BEGIN

print 'no files exists'

END

David McFarland SSChampion Points: 11815 More actions · Answer 4

Timothy, Christi straightened out the syntax, but the thing to remember is that SQL Server doesn't have an "IF/THEN/ELSE", it's just "IF/ELSE". While not absolutely necessary when only a single statement follows the IF or ELSE sections, it's a good practice to wrap both sections with BEGIN and END, as Christi did.