xp_cmdshell and return code 1

[cs2009]

Using SQL Server 2005 Standard Edition.

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I'm using xp_cmdshell in a stored procedure to copy a file. I purposely changed the file location and UNC path so that the file would not be found. I get this message in the proc output: The system cannot find the file specified. However, return code returns 0 and not 1 as I expected.

Does anyone know what I am doing wrong?

Thanks.

Exec @rc = sp_executesql @statement

If (@rc = 1)
	Begin
		RAISERROR ('IMS Copy Failure: %s.',
							16, 1, @statement)
	End

 

[bborissov]

You get the result from sp_executesql not from xp_cmdshell.
What you have in @statement variable?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[cs2009]

EXEC xp_cmdshell 'copy "\\server1\Drafting\Trans\IT0912180067.pdf" "\\server2\dropbox\test\cdraft\Trans\0912180067.pdf"'

 

[bborissov]

How about:

[COLOR=blue]DECLARE[/color] @Statement nvarchar(4000)
[COLOR=blue]SET[/color] @Statement = [COLOR=red]'EXEC @MyOut = xp_cmdshell ''copy ".pdf" ".pdf"'''[/color]

[COLOR=blue]DECLARE[/color] @TestXP [COLOR=blue]int[/color]
[COLOR=blue]EXEC[/color] sp_executesql @statement, N[COLOR=red]'@MyOut int OUTPUT'[/color], @MyOut = @TestXP [COLOR=blue]OUTPUT[/color]

[COLOR=blue]If[/color] (@TestXP = 1)
    [COLOR=blue]Begin[/color]
        [COLOR=blue]RAISERROR[/color] ([COLOR=red]'IMS Copy Failure: %s.'[/color], 16, 1, @statement)
    [COLOR=blue]End[/color]

NOT TESTED AT ALL!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[cs2009]

Borislav,

It appears I am getting nulls in the @TestXP variable. I've tried other ways, but still no luck.

DECLARE @TestXP int
EXEC sp_executesql @statement, N'@MyOut int OUTPUT', @MyOut = @TestXP OUTPUT
print 'xp return code = ' + Convert(Nvarchar(50),IsNull(@TestXP,999))

If (@TestXP = 1)
   Begin
      RAISERROR ('IMS Copy Failure: %s.', 16, 1, @statement)
   End

And the output:

xp return code = 999

(1 row(s) affected)

 

[bborissov]

Strange, it gives me 1 here?
BTW did you enabled xp_cmdshell?
By default it is disabled in 2005 and 2008.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[cs2009]

I do have xp_cmdshell enabled. I'm stumped. Do you think it might have to do with service pack 1?

 

[bborissov]

George Mastros suggested you to use xp_fileexist SP to check the existence of the file first.

Something like that:

DECLARE @Test int    
DECLARE @FileName NVARCHAR(2000)
SET @FileName =   'full path to the file here'
exec xp_fileexist @FileName,@Test OUTPUT
IF @Test = 1 
   BEGIN
   --- the script fo xp_cmdshell
   END
ELSE
  --- Error message "File does not exists"

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[cs2009]

Borislav, Great! The xp_fileexist solution worked. A big thanks to you and George.

 

[bborissov]

All credits to George here.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.