Script Object as ALTER uses cached proc script, not current

EBourasEBouras Posts: 2
edited February 20, 2012 12:05PM in SQL Prompt Previous Versions
I ran into what I would consider a serious issue with the Script Object as ALTER feature. A few hours earlier I had changed a stored procedure script and executed the Alter script so the change was now "active".

A few hours later, within the same SSMS application (but different query window) I used the right-click Script Object option as a shortcut so I didn't have to scroll through a very long list in Object Explorer... Unfortunately it scripted the OLD version of the procedure and not the one changed hours earlier.

Now, I know this is because I didn't hit "Refresh Suggestions" (which I subsequently did and got the newer version), but my point is that this is exceptionally dangerous given an environment where changes are not always known by all individuals and where there is no DBA to manage all changes. I could have blown out hours of work or critical changes by executing the generated cache-scripted version and never known until all hell broke loose.

Is there a very compelling reason to use caching instead of just scripting the most current version (which SSMS does in pretty much the same amount of time)? Is it more compeeling than potentially causing changes to be whacked?

On a related topic, why can't suggestions automatically refresh at certain intervals or when a change has been detected to a certain object (by reading the metadata in sys objects or something like that)? If there is an auto-refresh feature then I recommend it be made the default behavior. Manually refreshing is such a drag and is really no improvement on the "refresh local cache" option is SSMS that constantly causes squigglies due to latency.
MS MVP (Visual Basic Language)

Comments

  • You make a good point. Generally, the reason things are done from cache is so that Prompt can achieve the performance necessary to maintain a smooth workflow, but there's not really a reason that scripting shouldn't pull the most recent version from the database, especially as the worst case scenario could cause a significant amount of damage to the database.

    Can I ask you to submit this as a feature request in the SQL Prompt UserVoice forum here:

    http://redgate.uservoice.com/forums/944 ... uggestions

    By using forums like this we can keep on top of which features users are particularly keen to get implemented, and other users can vote on your suggestions. I'll pass on your comments to the development team myself, but it would be really useful to have it up on UserVoice as well so other users can see and vote on it.

    Thanks for your feedback!
    Andy Campbell Smith

    Red Gate Technical Support Engineer
Sign In or Register to comment.