Post reply

Checkpoint in stored procedure

  • April 2, 2009 at 8:11 am

    #97550

    I have a high performance transactional database. I'm writing a maintenance stored procedure which would remove inactive users from the system & send out email notifications on some circumstances to managers & users.

    We would be running this stored procedure off hours but still is there a way to put a checkpoint (to start the procedure next time from where it left) within a stored procedure as I dont want to use transactions which would lock the database or table?

    Also, i'm using database mail to send email so i would be using a looping function to send emails to each user identified to receive emails.

    Any inputs are welcome.

    Thanks

  • April 2, 2009 at 12:42 pm

    #971002

    The only thing I can think of is have a small audit table which would contain the step name within the stored procedure.

    So your stored proc might be something like:

    Create proc MyProc

    @step varchar (10)

    As

    If @step = 'Step2'

    Begin

    goto Step2

    Else

    goto Step1

    End If

    Step1:

    Print 'This is Step1'

    Update AuditTable set AuditColumn = 'Step 1'

    Step2:

    Print 'This is Step2'

    Update AuditTable set AuditColumn = 'Step 1'

    Then run it off a script something like:

    Declare @step varchar(50)

    Select @step='top 1 AuditColumn from AuditTable'

    Exec MyProc @step

  • April 2, 2009 at 1:34 pm

    #971036

    unfortunately i cann't add another table to the database though this would help me run procedure in checkpoint.

    i have to create transaction within a loop to delete user & send an email in order to make sure all users & managers receive email.

  • April 3, 2009 at 2:39 am

    #971353

    Could you not create the table in tempdb?

  • April 3, 2009 at 6:52 am

    #971487

    yes. but once my job fails & restarts the tempdb table would not be available any more. moreover, global temp table may leave orphaned tables.

    Thanks

  • April 3, 2009 at 7:08 am

    #971505

    RJ (4/2/2009)

    unfortunately i cann't add another table to the database though this would help me run procedure in checkpoint.

    i have to create transaction within a loop to delete user & send an email in order to make sure all users & managers receive email.

    If you can't create a permanent table, is using the registry or file storage possible? Persistent state has to go somewhere, er, persistent!

    When you say 'delete user', do you mean ending their connection to the server? Seems a bit harsh! Also, KILL cannot be used inside a transaction.

    If you simply mean removing their login or database user credentials, you will need to check all the conditions for doing this successfully very carefully. The principal cannot own objects in the database for example. A simpler solution might simply to be to DENY CONNECT on the login, and administer the drops manually...?

    /Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • April 3, 2009 at 2:13 pm

    #971849

    Paul

    Users I meant custom users i.e. they can be salesman, buyer, seller, manager & not the users within the sql server.

    I have a user created table where users is a column in it....

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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