January 31, 2008 at 2:54 pm
In SQL Server 2005, whenever I want to modify an existing stored procedure, I left click the SP name (under the Programmability of my DB) and select Modify.
Unfortunately, the displayed SQL code for the SP carries a name like SQLQuery24.sql instead of the name of the SP. When I want to save the SP after making changes to it, I must select the name of that SP from a list of available SPs (or type in the SP name). Since the number of SP may be large, you can easily make a mistake and resulting in overwritting other SP with the one you modified. This is dangerous!
Is there a way to display a SP file name when you want to modify it? I think MS should always defaulted to the SP name instead of a new query name when modifying a SP.
Thanks for any suggestion.
sg2000
February 1, 2008 at 12:03 pm
sg2000,
I think you are going against the grain of how microsoft intended the modify action to be.
When you right-click and choose 'modify' it brings up the existing stored procedure which you can edit and then recompile back into the database where the stored procedure is.
The query that is pulled up is shown as SQLquery as an unsaved query just like any time you open a new query window.
The safety feature is that it is not brought up as the existing file (just in case you don't want to save the modification to the original) the manager studio makes you do a File/Save as on the query.
Basically it is a flip flop of what you desire but I think what is more dangerous is having a new query window come up with the existing sql query data file name. (plus how could the query analyzer now where the .sql file is stored?)
February 1, 2008 at 12:48 pm
jsheldon:
Thanks for the reply. However, I still think that when you click Modify on a SP, your main intention is to modify the existing SP; so why doesn't SQL Server shows the original SP name? If you want to save the SP as another SP or query, you can always overwrite the original SP name. In a number of occasions, I almost wiped out my existing SP by erroneously naming the modified SP as one that should not be modified.
As far as MS is concerned, when you click on an existing MS Word document (for that matter, an existing Excel, Power Point etc.), you always open up a file with the original file name, not something like newdocument1.doc. After making modification, you can just click Save and the modification will be saved in the original file. If you want to save it under other file name, you can always do a Save As. This is the logical approach.
I hope someone from MS SQL Server development team can respond with the logic behind the philosophy of a SP modify save.
Thanks.
sg2000
February 1, 2008 at 12:55 pm
I hear what you are saying but I think the Word doc analogy is comparing apples and oranges.
A .sql file is just that a file. It is not a stored procedure, view, table, etc, until you parse and execute into the server.
The actual stored procedure is not in the file but in your sysobjects table within your user database.
When you go to modify the stored procedure it is simply bringing up the stored procedure for editing purposes, once you hit execute it goes back into sysobjects.
The option to save it as a file, physically away from the server is your option via File/Save As.
In order for what you request, when you right-click modify, somewhere in sysobjects would have to be the mapping to the actual file. Would it be a UNC or a drive letter map? What if the drive was remapped to a different letter?
If you think in terms of compiling the code, you can see why the query editor comes up as an unsaved query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply