Hi Here is my store procedure. The below code is not giving error at all when dts fails.

Can any one please help me how to trap xp_cmdshell dtsrun error message

CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] A
exec USP_ReadVRUDirector
set nocount on
DECLARE @filename Char(20),@cmdstring varchar(150) ,@Error int
DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FO
SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by filelis
OPEN VRUCFileListCurso
FETCH NEXT FROM VRUCFileListCurso
INTO @filename
WHILE @@FETCH_STATUS =
BEGIN
truncate table VRUSURVEYComment

set @cmdstring = 'copy e:\ftproot\vru\'+@filename +' e:\ftproot\vru\VRUComments.txt
exec master..xp_cmdshell @cmdstrin

exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyComments'


SELECT @Error = @@ERRO

IF @Error <>
BEGI
Print @Erro
EN


UPDATE VRUSURVEYComments SET [Filename] = @filename -- where [Filename] is nul

update VRUSURVEYComments set cur= 'T'
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyCommentsExport
FETCH NEXT FROM VRUCFileListCurso

INTO @filenam
EN
CLOSE VRUCFileListCurso
DEALLOCATE VRUCFileListCurso

Hi

Rather than using xp_cmdshell to rename the file try using something like
the following to change the name of the source file. Using the FSO you can
check errors more easily!

http://www.sqldts.com/default.aspx?200
http://www.sqldts.com/default.aspx?246

You should also check the return value from xp_cmdshell e.g

DECLARE @cmd sysname, @var sysname, @stat int
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > G:\var_out.txt'
EXEC @stat = master..xp_cmdshell @cmd
PRINT '@stat= ' + CONVERT(varchar,@stat)

John

"Admin" <admin (AT) emoneylinks (DOT) com> wrote