I currently have a stored proc that calls many functions.

One of them is running a DTS job that will transform data
from a sql DB to an output file.

I use the following line of code in the stored proc.

EXEC @Result =
master..xp_cmdshell "DTSRun /SSQLCLUST /Usa /Psa /NDTS_Orde
rDetailAttach", NO_OUTPUT

When this runs I can see that the DTS job completes
successfully. However, @Result never gets set to
successful.


I know in a SQL job you have the option to say on success
or on failure do <action>. So there must be a way to
evaluate wether the DTS was successful or not.

Any Ideas on how I can determine the Return status of the
DTS job?

Thank you.

In article <02f101c38f5d$8f4f1140$a101280a (AT) phx (DOT) gbl>, Joel Ewald
<joel.ewald (AT) logisysinc (DOT) com> writes
I would dump the contents into a table, and then check that

CREATE TABLE #DTS (Result varchar(8000) NULL)

INSERT #DTS
EXEC xp_cmdshell 'DTSRUN...'

Now query #DTS for failure text and such like.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thanks

that is the work around I had already put in place.

Just thought there might be a better way either using some
kind of dta global output variable or extended stored proc
command.

You could use the sp_OA stored procedures as this will allows you to get the
error info from each step.


--
Darren Green
http://www.sqldts.com

"Joel Ewald" <joel.ewald (AT) logisysinc (DOT) com> wrote