[9.0.176 Pro] - Database Explorer Caching snafu

[9.0.176 Pro] - Database Explorer Caching snafu

I have another question about the Database Explorer and how it caches objects.

I've been working on a stored procedure that's most of the way there. However I've opened it a few times in SQL Editor from the Database Explorer, and I swear things I've changed are not present.

This morning I decided to try and track it down.

Step 1: Change a comment from lower case to upper case in SSMS. Hit F5 to alter the procedure.

Step 2: Go find the procedure in Database explorer, right click and do "Show DDL". This shows that the comment I changed is indeed upper case.

Step 3: Right click the procedure and click Edit Procedure. A new tab opens, but the upper case comment is still lower case. Close Tab.

Step 4: Hit the refresh button on the database explorer.

Step 5: Re-open the procedure via right click "Edit Procedure", nope it's still got the lower case comment. *scratch head*

Step 6: Try refresh cache from the context menu

Step 7: Reconfirm the SPROC still hasn't changed via "Edit Procedure" right click. *scratch head*

Step 8: Open an entirely new tab (Ctrl-N) without invoking "Edit Procedure", drill down to the procedure and right click and Edit Procedure.. see your change is now there.

This is reproducible. Why didn't refresh cache catch it, even if the refresh in D.E did not?

To the best of my knowledge the text of procedural objects isn't cached. Every time you use Edit Procedure or Show DDL, it goes to the database server to retrieve the procedure code.

I'm sorry for asking a silly question, are you connected to the same server and database in both places?

	SysOp wrote:
	I'm sorry for asking a silly question, are you connected to the same server and database in both places?

Yes I am.

I too thought maybe it wasn't cached because "Show DDL" showed the appropriate changes, but "Edit Procedure" did not.

Here's a quick video:

http://screencast.com/t/OQgmP4wiMP

It also works strictly in SQL Editor as well.

Have 2 tabs open, edit the PROC in one tab and execute the code then go edit it in another.. it won't have the new changes, even after refresh cache and D.E refresh have been clicked.

Can you reproduce that if you click Procedures folder in DE and then refresh? Or schema folder?

I have a feeling that kind of refresh on the specific procedure name "folder" is refreshing what goes below it, namely procedure parameters and extended properties, not the procedure itself.

PS. Sorry for the confusion, I wasn't aware that caches procedure code as well. I learned something new today.

	SysOp wrote:
	Can you reproduce that if you click Procedures folder in DE and then refresh? Or schema folder? I have a feeling that kind of refresh on the specific procedure name "folder" is refreshing what goes below it, namely procedure parameters and extended properties, not the procedure itself. PS. Sorry for the confusion, I wasn't aware that caches procedure code as well. I learned something new today.

It doesn't matter where I am in DE when I click refresh. Nothing seems to refresh it except an entirely new tab.

I think I've lost some code due to this. I'll have several tabs open and close them now and then and then pull in my procedure and notice it doesn't appear right compared to what I recall. I haven't checked it into SCS yet so I can't go through a git history.

I'm submitting a bug report for this issue.

To get back the code lost, please use recent documents tool, and set a filter by contents for something that is unique to your lost changes. I hope it will find the lost revision.

Thanks for putting a bug report in.

Our developers are investigating this issue and it looks like they were able to reproduce it. They think it might be actually a bug and not intended behavior. The text of the procedure should not be cached.

	SysOp wrote:
	Our developers are investigating this issue and it looks like they were able to reproduce it. They think it might be actually a bug and not intended behavior. The text of the procedure should not be cached.

Agreed. Leads too stuff like this! ;)

Any updates on this one? Perhaps related to the auto refresh cache checkbox in DE now?

There seem to be several related tickets updated at different times. I believe this issue has been fixed in 9.1 too.