SA: Cache not refreshing when altering procedure?

Okay, so I've got an SASE window up with exec dbo.FooBar

I hover over and click edit hyperlink, modify my procedure and hit F5 to make changes permanent

I close that editor window and come back to exec dbo.FooBar tab.

I hover over and click edit hyperlink again and see the procedure didn't save my changes.

I hit Ctrl+Shift+F8 (refresh cache) just in case.

Again back at exec dbo.FooBar and hover over and hit code link, code window appears with changes.

I then go do the edit hyperlink again, still has the stale code.

If I use Ctrl+W and find the procedure and select "edit procedure" the code is no longer stale.

So if this is a stale cache problem, why didn't Ctrl+Shift+F8 solve it?

https://www.screencast.com/t/cqBFZsIkmf

example

I think I can demystify some parts of what you observed and also confirm the deficiency impacting the procedural code view/edit, there is a still an old outstanding ticket for it, which is still not fixed. Each editor tab is fully independent. Technically that's not exactly the case, but for our purposes here, let's assume that's the case. By fully independent I mean each editor tab maintains its own database connection and the connection related cache. It also maintains its own instance of the Database Explorer populated from that connection. If you enable the Automatic Cache Refresh option, and execute DDL operations SQL Assistant usually can detect them,unless they're burred deep inside procedures, then the Database Explorer, contents of Intellisense popups, and some other cache related things refresh automatically. Here by DDL changes we mean CREATE / ALTER / DROP operations impacting contents of the Database Explorer and contents of Intellisense popups. Procedural code change don't fall into that group, and not refreshed automatically.

There is a known workaround. In the popup, click the Procedure name hyperlink in the top-left corner instead of the Edit hyperlink. This one retrieves procedure code and properties from the database each and every time you click it.

SysOp wrote:

I think I can demystify some parts of what you observed and also confirm the deficiency impacting the procedural code view/edit, there is a still an old outstanding ticket for it, which is still not fixed. Each editor tab is fully independent. Technically that's not exactly the case, but for our purposes here, let's assume that's the case. By fully independent I mean each editor tab maintains its own database connection and the connection related cache. It also maintains its own instance of the Database Explorer populated from that connection. If you enable the Automatic Cache Refresh option, and execute DDL operations SQL Assistant usually can detect them,unless they're burred deep inside procedures, then the Database Explorer, contents of Intellisense popups, and some other cache related things refresh automatically. Here by DDL changes we mean CREATE / ALTER / DROP operations impacting contents of the Database Explorer and contents of Intellisense popups. Procedural code change don't fall into that group, and not refreshed automatically.

There is a known workaround. In the popup, click the Procedure name hyperlink in the top-left corner instead of the Edit hyperlink. This one retrieves procedure code and properties from the database each and every time you click it.

If each tab contains its own cache then why does the code hyperlink show the changed procedure?

Why does manually refreshing the cache not reflect the proper code afterwards ?

Unfortunately the code caching is a separate thing, not part of the main cache and that's why refreshing main cache doesn't help here. It's by design for supporting some other internal functions, but causing unwanted side effects like this one. They need to be fixed.

Okay, thanks for clearing it up. Hope a fix comes eventually, I think it’s bitten me in the butt a few times thinking I didn’t commit my code.