4,707 questions
What you are describing sounds like a problem in the version of SSMS you are using.
Please update to the current version and retest.
Issue with SQL Server saving Stored Procedure Changes
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 81%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJD%3C/text%3E%3C/svg%3E)
Jeff Dosser
21
Reputation points
I'm having a troublesome issue with stored procedures on my SQL Server version 13.0.4001.0.
I've noticed this problems many times before, but it's become quite problematic.
Here is the scenario. Inside SQL Server, I open a stored procedure for edit. In this example,
If I make a change to the sproc code (example, commenting to lines), when I hit 'execute'
after making these changes, there are no errors and everything seems 'ok'.
However, regardless of how many times I 'refresh' and regardless at what 'level' I refresh,
sometimes the odds of the change actually being made in the sproc is less that 1/3.
I'm writing this message after 5 attempts to make a 'save' to an sproc after a simple change
of commenting out 2 lines.
I hit 'execute' multiple times, refreshed at all levels of the data base: the sproc itself, 'stored procedures'
'programmability and still the odds of the code change actually 'taking' are slim.
Any help much appreciated.
Developer technologies Transact-SQL
Accepted answer
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Tom Phillips 17,771 Reputation points2021-09-02T14:16:29.517+00:00 -
Jeff Dosser 21 Reputation points
2021-09-02T15:29:58.193+00:00 I'll try the update and see if that solves the problem.
To answer an earlier question, the 'Refresh' I'm referring to is 'Refresh' option you see when right-clicking on any
of the DB folders along the structure chain. Ex: the database itself, 'Programmability', "Stored Procedures', and the sproc itself, all have a 'Refresh' when right-clicked.
My thought was that maybe the change has been made in the sproc, but not reflected in whatever cache or internal space SS might be using.
(Not familiar with SQL Server (SS) internals)Is it possible something either hardware or network related (in terms of the server SS is running on), could be responsible for such an odd occurrence? I've noticed that the ability to 'save' changes seems to increase or decrease in seemingly random ways. I'll not have any issues
for weeks, then like yesterday, I'll not be able to save a code change for several minutes.For now, I'll hit the answer as accepted and see if the version upgrade doesn't fix me.
-
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator2021-09-02T21:00:46.83+00:00 My thought was that maybe the change has been made in the sproc, but not reflected in whatever cache or internal space SS might be using.
(Not familiar with SQL Server (SS) internals)So, let's try to straighten this out. There are two programs involved. SQL Server and SQL Server Management Studio. SQL Server is a database server and runs as a service, and does not have a UI.
You can only communicate with it over a certain protocol, known as TDS. To perform this communication, you need an application of some sort. This can be a purpose-written application like an order-entry system, an ERP system or whatever. It can also be a generic query tool such as SQL Server Management Studio, SSMS.
SSMS itself consist of a query editor, the Object Explorer and a number of dialogues that helps you to build SQL commands. When you edit a stored procedure and run the command ALTER PROCEDURE, the new version procedure is deployed to the database that is current for your window. The Object Explorer does not know about this, so if you create a new procedure, you will not see it in the Explorer tree with less than if you do Refresh.
However, you were talking about changing existing procedures, and then you say that you don't see the change after the refresh. Since the Explorer tree only shows the name and the parameters, changes to the code cannot be reflected in the tree. Thus, I'm at loss to understand what you are actually seeing.
Sign in to comment -
2 additional answers
Sort by: Most helpful
-
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
2021-09-01T21:45:38.64+00:00 First, you seem to be behind on the SQL Server version. I think that you have SQL 2016 SP1. The most recent build is SP2 CU16, so you would download first Service Pack 2 and then Cumulative Update 16. Not that it will change your problem.
When you say that you are doing "refresh", what are you actually doing? Do you script the procedure anew in SSMS?
What to you see if you run "sp_helptext yoursp" from a query window?
-
MelissaMa-MSFT 24,221 Reputation points2021-09-02T06:10:10.417+00:00 Hi @Jeff Dosser ,
Welcome to Microsoft Q&A!
Where did you open a stored procedure for edit? Is it in the SSMS(SQL Server Management Studio)? If yes, what is the version of your SSMS?
I tried with
SSMS v17.4and all comments did not show after right-clicking the procedure and clicking the Modify.But the comments remained in
SSMS v18.5.You could upgrade your SSMS to latest version and have another try.
Best regards,
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.