How to trap xp_cmdshell dtsrun error messages

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 messages
CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] AS
exec USP_ReadVRUDirectory
set nocount on
DECLARE @.filename Char(20),@.cmdstring varchar(150) ,@.Error int
DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FOR
SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by filelist
OPEN VRUCFileListCursor
FETCH NEXT FROM VRUCFileListCursor
INTO @.filename
WHILE @.@.FETCH_STATUS = 0
BEGIN
truncate table VRUSURVEYComments
set @.cmdstring = 'copy e:\ftproot\vru\'+@.filename +' e:\ftproot\vru\VRUComments.txt'
exec master..xp_cmdshell @.cmdstring
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyComments'
SELECT @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
Print @.Error
END
UPDATE VRUSURVEYComments SET [Filename] = @.filename -- where [Filename] is null
update VRUSURVEYComments set cur= 'T'
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyCommentsExport'
FETCH NEXT FROM VRUCFileListCursor
INTO @.filename
END
CLOSE VRUCFileListCursor
DEALLOCATE VRUCFileListCursor
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@.emoneylinks.com> wrote in message
news:12AE78DE-091E-4E43-BEAA-639F1EAA6E4C@.microsoft.com...
> 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 messages
> CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] AS
> exec USP_ReadVRUDirectory
> set nocount on
> DECLARE @.filename Char(20),@.cmdstring varchar(150) ,@.Error int
> DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FOR
> SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by
filelist
> OPEN VRUCFileListCursor
> FETCH NEXT FROM VRUCFileListCursor
> INTO @.filename
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> truncate table VRUSURVEYComments
> set @.cmdstring = 'copy e:\ftproot\vru\'+@.filename +'
e:\ftproot\vru\VRUComments.txt'
> exec master..xp_cmdshell @.cmdstring
> exec master..xp_cmdshell 'dtsrun /S
WPCCP005DS /E /N VRUSurveyComments'
>
> SELECT @.Error = @.@.ERROR
> IF @.Error <> 0
> BEGIN
> Print @.Error
> END
>
> UPDATE VRUSURVEYComments SET [Filename] = @.filename --
where [Filename] is null
> update VRUSURVEYComments set cur= 'T'
> exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N
VRUSurveyCommentsExport'
> FETCH NEXT FROM VRUCFileListCursor
> INTO @.filename
> END
> CLOSE VRUCFileListCursor
> DEALLOCATE VRUCFileListCursor
>
>

How to tracking trigger

Hi guys,
I'm using trigger to write multiple files (5-6 file on multiple directories)
using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
I've found that sometime it does not output all the files it should be
written (almost the last file). So I'd like to check what happen on my
trigger. Could you please suggest me?
Thanks in advance,
Thana N.Thana
Run SQL Server Profiler to capture SP:StmtCompleted event.
"Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I'm using trigger to write multiple files (5-6 file on multiple
directories)
> using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> I've found that sometime it does not output all the files it should be
> written (almost the last file). So I'd like to check what happen on my
> trigger. Could you please suggest me?
> Thanks in advance,
> Thana N.
>|||Uri,
Is there anyway to track the historical for this? Because it is running on
the production and I cannot have a right to run Profiler for this (my sql
user is not in a group of sysadmin).
Thanks for reply,
Thana N.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ONJrz0S9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thana
> Run SQL Server Profiler to capture SP:StmtCompleted event.
>
> "Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
> news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > Hi guys,
> >
> > I'm using trigger to write multiple files (5-6 file on multiple
> directories)
> > using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> >
> > I've found that sometime it does not output all the files it should be
> > written (almost the last file). So I'd like to check what happen on my
> > trigger. Could you please suggest me?
> >
> > Thanks in advance,
> > Thana N.
> >
> >
>

How to tracking trigger

Hi guys,
I'm using trigger to write multiple files (5-6 file on multiple directories)
using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
I've found that sometime it does not output all the files it should be
written (almost the last file). So I'd like to check what happen on my
trigger. Could you please suggest me?
Thanks in advance,
Thana N.Thana
Run SQL Server Profiler to capture SP:StmtCompleted event.
"Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I'm using trigger to write multiple files (5-6 file on multiple
directories)
> using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> I've found that sometime it does not output all the files it should be
> written (almost the last file). So I'd like to check what happen on my
> trigger. Could you please suggest me?
> Thanks in advance,
> Thana N.
>|||Uri,
Is there anyway to track the historical for this? Because it is running on
the production and I cannot have a right to run Profiler for this (my sql
user is not in a group of sysadmin).
Thanks for reply,
Thana N.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ONJrz0S9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thana
> Run SQL Server Profiler to capture SP:StmtCompleted event.
>
> "Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
> news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> directories)
>