Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
 Posted - 2002-10-16 : 14:33:23
|
| I'm running a file copy procedure and capturing text based on existence tests. If the existence test come back false, I want to fail the procedure with a certain error message. I have the raiserror down fine. However, when I run the procedure from DTS, it shows that it ran fine. How do I exit the procedure in a way that will indicate a failed step to DTS?Derrick Leggettderrick_leggett@hotmail.com |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-10-16 : 19:07:56
|
| Why not use FileSystemObject in an ActiveX Script then you can explicitly set the return status of the step depending on whether the file exists by either returning DTSTaskExecResult_Failure or DTSTaskExecResult_Success.HTHJasper Smith |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2002-10-17 : 10:39:10
|
| That would be one way to do it. However, there is already a LOT of script written that is entirely SQL. The actual copies, dir checks, unzips, etc are done through a command line that feeds results to temp tables. These are then analyzed and a table is fed log text as things are verified or found to be in error. Since this it's so important to log each step, I really need to keep it in SQL. The reason I'm trying to fail the step through the procedure is to gracefully fail the step, while maintaining my logging correctly.I have tried a raiserror with severity 19, which should automatically fail the procedure and cause the step to show failure in DTS. The raiserror 19 is logged, but the step still shows as being executed successful.Confused on my part...DerrickDerrick Leggettderrick_leggett@hotmail.com |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-17 : 13:20:07
|
| In SQL 7 ....In the Package Properties dialog, check the "fail package on first error" option ...In SQL 2k, you have a bit more flexability ....In the workflow properties of your execute sql task, on the options tab, check the "fail package on step failure" option ...Jay White{0} |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-17 : 14:06:56
|
quote:
In SQL 7 ....In the Package Properties dialog, check the "fail package on first error" option ...In SQL 2k, you have a bit more flexability ....In the workflow properties of your execute sql task, on the options tab, check the "fail package on step failure" option ...EDIT: it seems like there should be a way to fail and halt a pkg from an activex script, but I can't seem to figure it out at the moment.Jay White{0}
Jay White{0} |
|
|
|
|
|
|
|
|
Error and Fail a procedure