Post reply

DTS + Logging + Transactions + SP = Impossibele ?

  • January 3, 2005 at 1:21 am

    #61208

    Hi !

    I am trying to make loggning in my DTS package that have some stored procedures. The package is also included in a transaction.

    Everytime my package will fail, there will be a rollback in my logging table

    The default SQL-Server package logging isen´t enought for my application.

    Must i write a "dirty" VB-hack that makes this logs in a separate db connection (without transaction support, and called as a extended stored procedure) ?

    If so, then the user would have to be a "admin-guy", only to make the logging feature to work

    Is this why SQL-Server itself writes the logs into a plain text-file on the filesystem instead of the msdb db ? That would explain this issue.

    But how do you guy´s handle this ? This (logging) must be the most basic need in a normal application.

    Any help is needed !

    Thanks.

    Johan.

     

  • January 3, 2005 at 5:42 am

    #535591

    use a @TABLE variable to store the errors.  These do not get rolled back the same as other items during a ROLLBACK TRANS. Why do I know this?  I have a very similar situation and it works a treat

    ON ROLLBACK condition I write to a @TABLE variable and AFTER the rollback I insert into my logging table the information from the @TABLE var.

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • January 4, 2005 at 3:55 am

    #535738

    Thanks for your answer AJ!

    I am sorry to say that i can´t follow you all the way.

    I use the DTS "automatic" transaction support and have no "BEGIN TRAN" ... in my stored procedures. So how could i "fetch" my @TABLE variable after the transaction throws the logic out from my stored procedure ?

    When there is a failure there will be a rollback and i can make a "failure" flow in the DTS. But in that stage the @TABLE variable will be lost/empty.

    Or am i missing something here ?

    regards.

    Johan.

  • January 4, 2005 at 4:30 pm

    #535887

    Adding the BEGIN TRAN..COMMIT isn't a big deal should be easy and BOL has decent examples.

    What you could do is in the SP that is being called write the information out using the @TABLE to whatever logging table you want and RETURN(@@ERROR).  This should throw you down the failure path where you can exit gracefully...



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply