Rollback Script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Rollback Script

Rollback Script

Rate Topic

Display Mode

Topic Options

Author

Message

Junglee_George

Posted Wednesday, September 05, 2012 2:59 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 477, Visits: 556

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

Post #1354387

derek.colley

Posted Wednesday, September 05, 2012 3:10 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 496, Visits: 586

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 http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Post #1354391

GilaMonster

Posted Wednesday, September 05, 2012 3:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:32 AM
Points: 38,030, Visits: 30,335

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 2008, MVP
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

Post #1354393

Junglee_George

Posted Wednesday, September 05, 2012 3:17 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 477, Visits: 556

Yes Gail, rollback script is what I need.
Can you pls help on this? Just a sample SQl Script will do.
Thanks

Post #1354395

Mike John

Posted Wednesday, September 05, 2012 5:06 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 2,645, Visits: 4,641

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

Post #1354434

GilaMonster

Posted Wednesday, September 05, 2012 7:11 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:32 AM
Points: 38,030, Visits: 30,335

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 2008, MVP
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

Post #1354505

Junglee_George

Posted Thursday, September 06, 2012 2:54 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 477, Visits: 556

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

Post #1355131

« Prev Topic | Next Topic »

Permissions