Script Object as ALTER uses cached proc script, not current
EBouras
Posts: 2
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.
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
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!
Red Gate Technical Support Engineer