Re: Copy File and error code

That would have been very easy to check most scenarios without asking!

DECLARE @ret int
DECLARE @sMsg varchar(3000)
DECLARE @sSource varchar(1000)
DECLARE @sTarget varchar(1000)

-- Source and destination do not exist
SET @sSource = '\\Server1\C\data\myDB.MDB'
SET @sTarget = 'D:\Data\myDB.MDB'
SET @sMsg = 'copy /Y "' + @sSource + '" "' + @sTarget + '"';
EXEC @ret = master.dbo.xp_cmdshell @sMsg, 'no_output'
SELECT 'Invalid source and destination: ' + CAST(@ret as CHAR(10))

EXEC @ret = master.dbo.xp_cmdshell 'ECHO "Hello World" > C:\temp\HelloWorld.txt', 'no_output'
SELECT 'Create source file: ' + CAST(@ret as CHAR(10))

-- Destination does not exist
SET @sSource = 'C:\temp\HelloWorld.txt'
SET @sTarget = 'D:\Data\myDB.MDB'
SET @sMsg = 'copy /Y "' + @sSource + '" "' + @sTarget + '"';
EXEC @ret = master.dbo.xp_cmdshell @sMsg, 'no_output'
SELECT 'Invalid destination: ' + CAST(@ret as CHAR(10))

-- Both are valid
SET @sSource = 'C:\temp\HelloWorld.txt'
SET @sTarget = 'C:\temp\HelloWorld2.txt'
SET @sMsg = 'copy /Y "' + @sSource + '" "' + @sTarget + '"';
EXEC @ret = master.dbo.xp_cmdshell @sMsg, 'no_output'
SELECT 'Two valid files: ' + CAST(@ret as CHAR(10))

-- Make the destination read only
EXEC @ret = master.dbo.xp_cmdshell 'ATTRIB +R C:\temp\HelloWorld2.txt', 'no_output'
SELECT 'Change to Read-only: ' + CAST(@ret as CHAR(10))

SET @sSource = 'C:\temp\HelloWorld.txt'
SET @sTarget = 'C:\temp\HelloWorld2.txt'
SET @sMsg = 'copy /Y "' + @sSource + '" "' + @sTarget + '"';
EXEC @ret = master.dbo.xp_cmdshell @sMsg, 'no_output'
SELECT 'Copy onto Read-only file: ' + CAST(@ret as CHAR(10))

John

"Scott" <sbailey@xxxxxxxxxxxxxxx> wrote in message news:u7GnW436JHA.728@xxxxxxxxxxxxxxxxxxxxxxx
If I say "no_output' as you suggest, after running it, I get 0. Is it safe to assume any error will be <> 0?



"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> wrote in message news:%23e%23eWu36JHA.5932@xxxxxxxxxxxxxxxxxxxxxxx
Use the NO_OUPUT option so you don't get that message and also use the return code to get success or failure:

DECLARE @ret int
EXEC @ret = master.dbo.xp_cmdshell @sMsg, 'no_output'
SELECT @ret --or whatever you want to do with it

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Scott" <sbailey@xxxxxxxxxxxxxxx> wrote in message news:ORpPle36JHA.4864@xxxxxxxxxxxxxxxxxxxxxxx
I'm copying a file across the network. My code works, but it returns the below results when successful. Is there any way to modify my code to force sql to return perhaps a simple 0 for success and maybe 1 for an error?


CODE:

DECLARE @sMsg varchar(3000)
DECLARE @sSource varchar(1000)
DECLARE @sTarget varchar(1000)

SET @sSource = '\\Server1\C\data\myDB.MDB'
SET @sTarget = 'D:\Data\myDB.MDB'

--SET @sMsg = N'copy ' + @sSource + N' ' + @sTarget
SET @sMsg = 'copy "' + @sSource + '" "' + @sTarget + '"';
EXEC master.dbo.xp_cmdshell @sMsg


RESULTS:

1 file(s) copied
NULL



.

Relevant Pages

  • Re: Copy File and error code
    ... return code to get success or failure: ... DECLARE @sMsg varchar ... DECLARE @sSource varchar ... EXEC master.dbo.xp_cmdshell @sMsg ...
    (microsoft.public.sqlserver.programming)
  • Re: Copy File and error code
    ... Is there any way to modify my code to force sql to return perhaps a simple 0 for success and maybe 1 for an error? ... DECLARE @sMsg varchar ... DECLARE @sSource varchar ... EXEC master.dbo.xp_cmdshell @sMsg ...
    (microsoft.public.sqlserver.programming)
  • Copy File and error code
    ... but it returns the below results when successful. ... DECLARE @sSource varchar ... EXEC master.dbo.xp_cmdshell @sMsg ...
    (microsoft.public.sqlserver.programming)
  • Re: Copy File and error code
    ... DECLARE @ret int ... Is there any way to modify my code to force sql to return perhaps a simple 0 for success and maybe 1 for an error? ... DECLARE @sMsg varchar ... DECLARE @sSource varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: file exist cmd in sql
    ... > you can make use of undocumented extended stored procedure ... > declare @ret int ... > Vishal Parkar ...
    (microsoft.public.sqlserver.server)
Loading