sp changes become undone????

Question

Post reply

sp changes become undone????

janniel

SSC Rookie

Points: 37
More actions
April 1, 2004 at 6:32 am

#76145

We have a little problem that's creating a lot of headaches for us.
Some of our sp's revert back to a previous version after we have implemented changes.(changes become undone).
Has anyone come across something similar?
Is it possible for something like this to happen?

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

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

Can you explain what changes become undone?

Can you post the code of the stored procedure?

janniel SSC Rookie Points: 37 More actions · Answer 2

racosta, thanks for replying.

It's the changes that we made in the sp's code.

Before change - exec vkbsp_getCost @ProductCode, @ContractRef, @Cost output

After change - exec vkbsp_getCost @ProductCode, @ContractRef, @CertificateRef, @Cost output

A few days after the code has been migrated to production one of our users would call with an error message. When investigating the problem, we would discover that the parameter we added is missing from the code. It's not only parameters that disappear, IF statements would revert back etc....

My feeling about this is that the person who migrated the code to production made some kind of mistake.

We have a very open system here. All 3(me included) developers login as 'sa' and implement changes in production. We do not have a dba so we do that part of the job as well.

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

"We have a very open system here. All 3(me included) developers login as 'sa' and implement changes in production. We do not have a dba so we do that part of the job as well."

Looks like it's time for a change-control mechanism to be implemented....and also a formal definition (and support) for a dba role.

SP's don't unwind by themselves. Somebody is possibly restoring the database and/or executing a script that re-creates the SP's.

janniel SSC Rookie Points: 37 More actions · Answer 4

Thanks Andrew, I have been trying to change the way things are being done around here. Do you have any suggestions for a change-control mechanism?

I did not think that the SP's could unwind by themselves, but one of the developers insist that that's wat happening. I just wanted to make sure that my thoughts on the subject was correct, as I am fairly new to SQL.

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 5

Have a look at the following site....he's an author I respect for sound and sensible advice.

http://www.nigelrivett.net/

in particular the information @

http://www.nigelrivett.net/SQLServerReleaseControl.htm

janniel SSC Rookie Points: 37 More actions · Answer 6

janniel

SSC Rookie

Points: 37

April 2, 2004 at 2:30 am

#501563

Thanks, it looks promising.

CAGreensfelder SSC Eights! Points: 919 More actions · Answer 7

Also look at Steve Jone's articles on Change Contol:

http://www.sqlservercentral.com/columnists/sjones/vcspart1.asp

We have had sprocs come "undone" but it was always because someone didn't get the good copy onto the master copy of the database before a backup/restore cycle. It's really easy to do with 4 developers working on 3 or 4 copies of the database.