Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rollback Script Expand / Collapse
Author
Message
Posted Wednesday, September 05, 2012 2:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:52 AM
Points: 588, Visits: 1,004
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
Posted Wednesday, September 05, 2012 3:10 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

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
Posted Wednesday, September 05, 2012 3:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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
Posted Wednesday, September 05, 2012 3:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:52 AM
Points: 588, Visits: 1,004
Yes Gail, rollback script is what I need.
Can you pls help on this? Just a sample SQl Script will do.
Thanks
Post #1354395
Posted Wednesday, September 05, 2012 5:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 2,663, Visits: 5,603
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
Posted Wednesday, September 05, 2012 7:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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
Posted Thursday, September 06, 2012 2:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:52 AM
Points: 588, Visits: 1,004
Thank You Gail n Mike..You guys really helped me..
Post #1355131
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse