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 |
|
reynoud
Starting Member
2 Posts |
 Posted - 2006-03-15 : 04:39:40
|
| Can you imagine this weird thing??You create a DTS 2000 package (Package A) which executes another package (Package B).Inside Package B, create simply an ActiveX task which causes the package to fail. (Main = DTSTaskExecResult_Failure)Also go to the Package Properties and in 'logging' select 'Log package execution to SQL Server’ (fill in what's needed). Do not select 'fail package on log error'. Now save Package B.In Package A, create the task to execute Package B. Then, create two ActiveX tasks: in the first one type 'call msgbox("execution failed")' and in the other one type 'call msgbox("execution successful")'Create an on-success workflow and an on-error workflow to the ActiveX tasks. Save and execute Package A. You should get the right result: you see a messagebox: 'execution failed'.Ok no problem so far, here comes the big trick. Create an on-insert trigger in [msdb].[dbo].[sysdtspackagelog]. Write something invalid that would cause an error on exection, e.g.: select 0/0 from sysdepends. After this execute Package A again.What happens? Exactly, you see a new messagebox: execution successful. But that's not right!? Package B failed, but still seems to be succesful?? How come?Ok, in reality you don't make such messy trigger, but what when you have one which calls a Stored Procedure which accidentally ends up in an error?This is the case with me now. It took a long time to discover this. This problem occurs to me only in sub packages. It doesn't matter turning on 'Fail package on first error' in the child package.Is this a bug, or what to think about this?Any ideas? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-16 : 08:15:56
|
| In reality, you shouldn't be creating triggers on system tables.. If you do, then you risk this kind of behaviour throughout your server.. |
 |
|
|
reynoud
Starting Member
2 Posts |
Posted - 2006-03-16 : 09:04:16
|
| Thanks RickD, although this is not the kind of answer I expected, I surely don't mind to clarify the reason of having a trigger on this table: it's a part of a framework which reports failures and transactions to a different database as well. Any ideas of solving this issue would be highly appreciated!Thanks! |
|
|
|
|
|
|
|
|
Package logging