xp_cmdshell copy command seldom fails

Question

I am running SQL Server 2005 on Windows Server 2003 machine.

I have a requirement to accumulate small text files into a bigger one.

So I use

exec xp_cmdshell @sql

where @sql=

'copy /b'+@sourcePath+@sourceFile+' '+@destinationPath+@NewFileName

Both the source and destination paths are on a separate server.

Seldom this process fails and I don't find anything else in the event or SQL Server logs.

The Surface Area Config for xp_cmdshell is also enabled.

Please help.....

Answer 1

I just tested this on my sql server 2005 and EXEC dbo.xp_cmdshell always returns output (even in the case of a bogus command) in the form of a table. For C#, if you call this code with ExecuteNonQuery, then call it with ExecuteReader and read the output. Alternatively, you could dump the output in a table so that you can look at it later at your leisure. Create a table like this :

CREATE TABLE [dbo].[xp_cmdShellOutput](
    [errorMsg] [nvarchar](max) NULL
)

and then use this code :

DECLARE @sql AS VARCHAR(600)

SELECT  @sql = '<your command>'

INSERT  dbo.xp_cmdShellOutput(errorMsg)
EXEC dbo.xp_cmdshell @sql