What are the challenges you face when working across database platforms? Take the survey
Options

Stored Procedure Versioning

MikeONeillMikeONeill Posts: 131
edited July 14, 2008 3:47AM in SQL Prompt Previous Versions
A little quirk I just spotted .

If I type EXEC SPName and then hover over the SPName , I get a hyperlink to show me the script of the SP , that's great.

If I now change the stored proc and repeat the exercise , I get the OLD version , not the one I have just modified .

If I now refresh the cache , I get the new one.

This caused my several minutes of confusion where a production DB had been updated with new stored procs and was failing but when I investigated I saw the OLD one which worked fine , but production was still failing . Eventually I loaded the sp from te MSSM object browser and spotted the difference.

Is this a bug or by design. Its highly confusing , but it was at 6am !!


Cheers

Mike

Comments

  • Options
    I bet it's by design.

    But it's made me think - how about making it a config option to always get the latest version when showing the helptext for an object. And/or include a 'get latest' button on the hoverwindow that opens
  • Options
    MikeONeill wrote:
    A little quirk I just spotted .

    If I type EXEC SPName and then hover over the SPName , I get a hyperlink to show me the script of the SP , that's great.

    If I now change the stored proc and repeat the exercise , I get the OLD version , not the one I have just modified .

    If I now refresh the cache , I get the new one.

    This caused my several minutes of confusion where a production DB had been updated with new stored procs and was failing but when I investigated I saw the OLD one which worked fine , but production was still failing . Eventually I loaded the sp from te MSSM object browser and spotted the difference.

    Is this a bug or by design. Its highly confusing , but it was at 6am !!


    Cheers

    Mike

    Hi Mike,

    Am not really sure I have understood the issue correctly, so correct me if I am wrong.

    When you say you changed the SP, did you actually refer to editing the actual SP script or choosing a different SP in the EXEC statement?
    If it was about editing the SP, then SQL Prompt will have to be refreshed manually or you will have to open a new query window for you to see the updated changes on the schema panel (which is the window that displays the SQL script of the object when you click on the object definition tooltips).
    But if you were referring to changing the name of a SP in a EXEC statement, the object definition tooltip should automatically get updated and display the new SP information in the tooltip and in the schema panel.

    I hope that explains and if you have further questions or if I got messed up with my understanding please let me know.

    Thanks,
    Tanya
    Project Manager
    Red Gate Software Ltd
  • Options
    moff wrote:
    I bet it's by design.

    But it's made me think - how about making it a config option to always get the latest version when showing the helptext for an object. And/or include a 'get latest' button on the hoverwindow that opens

    Hi,

    You got your money on the bet :)

    Nice suggestion...we have also been thinking of implementing an automatic cache refresh when any object changes in the database so handle such scenarios. It is currently under review and you will probably see it happen in one of our future editions.

    Thanks,
    Tanya
    Project Manager
    Red Gate Software Ltd
  • Options
    Hi Tanya

    I can be a bit vague !!

    I use the facility of hovering over a name to see the script of the SP , if I have a line that is EXEC spName , I use the hover to get the script , copy it and paste it to a new window so that I can see whats going one in the called SP.

    If the called SP has been changed since the last Cache Refresh , its shows me the OLD version , If I refresh the Cache I see the NEW one.

    I think you've answered it ,if any changes are made they only reflect after a manual update or Cache refresh.

    It would be nice , that if the schema of a SP is copied to clipboard , that it be the live one , not the Cached one .

    Using the hyperlink to get the text of an SP is nice feature ,just in this instance , a bit confusing.

    Cheers
    Mike
  • Options
    Hi Mike,

    I can understand your situation. As I had already mentioned this is in our list and the plan is to address this in our future versions.
    But until then, I reckon we do not have much choice :oops:
    Have a great week ahead.

    Thanks,
    Tanya
    Project Manager
    Red Gate Software Ltd
Sign In or Register to comment.