I agree with you Johan, it is important to remember that unlike their corresponding DDL events, DDL triggers do execute synchronously and in the source execution context. Consequently they do always have some effect on the source process and activity.
My general rule of thumb is that you should always prefer to use DDL Event notification over DDL triggers, unless you actually do want to have some effect on the triggering process or command (such as intentionally rejecting certain Logins).
Excellent war story.
I think Rbarryyoung is right on the money. I believe that the lack of explicit transaction demarcation is the real problem.
Do you mind specifying explicit "begin tran"/"commit tran" ? It should work, right?
Well, I will try to test it in collaboration with my biztalk dev team.
But I think it would be over simplified to just incorporate the explicit "begin tran"/"commit tran". E.g. when using implicit transactions, one should also have to check for the @@trancount !, and then basically violate the concept of the implicit transaction itself.
Secondly, considered the absence of the need to interfere in realtime, the only valid approach is the one using sqlserver events.
Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere
- How to post Performance Problems
- How to post data/code to get the best help
- How to prevent a sore throat after hours of presenting ppt ?"press F1 for solution", "press shift+F1 for urgent solution"
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me
but most of the time this is me