Post reply

Rollback Script

  • September 5, 2012 at 2:59 am

    #280809

    Hi

    I have a simple stored procedure having a select statement.

    Recently I added a NOLOCK to the table name in the SP.

    My boss now wants a rollback script to be added in SP to go back to the previous version, ie, version in which NOLOCK is not there in the SP.

    Any help is grateful

  • September 5, 2012 at 3:10 am

    #1533682

    Surely just remove the WITH (NOLOCK) hints in your stored procedure?

    Or am I missing something?

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • September 5, 2012 at 3:12 am

    #1533684

    Rolling the change back is probably a good idea...

    See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    The rollback script will just be an ALTER PROCEDURE with the definition of the proc prior to the change that you made.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 5, 2012 at 3:17 am

    #1533685

    Yes Gail, rollback script is what I need.

    Can you pls help on this? Just a sample SQl Script will do.

    Thanks

  • September 5, 2012 at 5:06 am

    #1533720

    There is no generic "rollback script" for this sort of change that anyone can give you.

    You need to take the soure of the procedure, and change it to remove the change you made last week.

    It is a very good idea to have some form of version control system to hold all stored procs, triggers, etc etc. We use Subversion - but there are many others. I fyou do not have that in place you can always script ethe procedure as it is now from the database.

    Mike

  • September 5, 2012 at 7:11 am

    #1533785

    Junglee_George (9/5/2012)

    Yes Gail, rollback script is what I need.

    Can you pls help on this? Just a sample SQl Script will do.

    Thanks

    Sure.

    ALTER PROCEDURE <the name of the procedure here> (< the parameters of the procedure here>)

    AS

    <the text of the procedure as it was before you made the changes>

    GO

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 6, 2012 at 2:54 am

    #1534312

    Thank You Gail n Mike..You guys really helped me..

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

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