Today an alter procedure statement was called. The alter statement was made, the correct DB using statement was in place, and immediately SSMS returned 'completed successfully' to the DBA. The proc being run in production continued to show old results however. After verifying with users the DBA right clicked on the object and generated schema, and the resulting script from SSMS showed the original code not the code as scripted in the alter statement.
The window with the alter statement was still up, so F5 was hit again. Same results. 'Completed Successfully' displayed by SSMS, while scripting the stored proc out reveals old version still in place.
After hitting F5 repeatedly, say maybe 20-30 times, the users finally started reporting that the code was working, and scripting out the object revealed the alter statement had finally taken effect.
When modifying schema definition, I know SQL Server takes schema locks to ensure that active queries and processes are consistent, but that didn't seem to be the case here as SSMS was reporting 'completed successfully' almost immediately.
Does SSMS report success when modifying some schema objects before it actually is successful? What is the isolation level of querying changes to programmable object definitions, i.e stored procs. I would think altering any object would delay the success result from SSMS until the schema lock had cleared and SQL Server had actually committed the change to the system object storage on which the catalog views depend. For example an alter table definitely does. This can take a significant amount of time depending on what changes are meta data only versus what changes require row by row conversions. The command is not reported as successful on an alter table statement until actually done.
Is that not the case for programing objects such as stored procs and functions?