Refresh Suggestions Doesn't

[CodantiM]

If a table is updated in a different DB and you select the "Refresh Suggestions" the changes are not picked up.

Say you create a table:
CREATE TABLE Model.dbo.Test (Test VARCHAR(1) NULL);

and then with Master as your current DB you have this code:

SELECT
*
FROM Model.dbo.Test T
WHERE
T.<cursor>

SQL Prompt will suggest the Test column. Now say you issue this command:

ALTER TABLE Model.dbo.Test ADD test2 VARCHAR(2) NULL;

and then refresh suggestions. Now will notice that test2 is not suggested for the above query. If you change your current DB to Model, refresh suggestions, and then change your current DB back to Master you will get the correct suggestions.

Should refresh suggestions refresh the suggestions for all DBs called in the query?

[CodantiM]

Essentially I expect "Refresh Suggestions" to completely clear the cache so that it was just like you started SSMS and SQL Prompt has to look everything up.

[Brian Donahue]

Hi Michael,

I think it would help to clarify two points:

• Refreshing suggestions applies only to the database to which you are currently connected.
  
• SQL Prompt automatically refreshes a database's objects when you close down your query editor application, then re-open it and re-connect to that database.

_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[CodantiM]

Brain,

If that is the way it is designed to work can I suggest that you add another option "Clear Suggestion Cache"?

It seems like a lot of unnecessary work if I am working on a query that uses 4 databases, and changes are made to objects in all of them that to get correct suggestions I either have to close SSMS and re-load everything and get back to where I was or:

* Refresh Suggestions
* Change current DB to DB2
* Refresh Suggestions
* Change current DB to DB3
* Refresh Suggestions
* Change current DB to DB4
* Refresh Suggestions
* Change current DB to DB1

If I am using a linked server and an object is updated on the remote server how can I refresh the suggestions? (I can't change the current DB to to the remote DB.) Is my only option to completely exit SSMS and then re-load everything?

[Cowski]

[Cowski]

I created a new table, tried refreshing the suggestions in SQL Prompt, also disconnected & reconnected to my SQL Server instance. The new table still would not show up.
But once I closed out SSMS & started it up again, the new table was finally there.

This is truly disturbing for a program that's so handy, useful...and expensive.

I hope this is fixed in a future version that my company will be able to get as a free upgrade.

[CodantiM]

It looks like if you go into options and disable SQL Prompt and then go back and enable SQL Prompt that the cache is cleared and SQL Prompt will refresh everything.

Can someone from Red Gate confirm that?

If so, I would think it should be fairly easy to give us a "Clear Entire Cache" option. Or at the very least put the Enable/Disable option back directly on the SQL Prompt 4 menu so we can toggle it quickly.

[CodantiM]

I just confirmed that disabling SQL Prompt and re-enabling it causes it to refresh a portion of the cache.

It appears to clear the cache for the connected server, but not for linked servers. i.e. I was creating a query in DB1 that referenced tables in DB2, I added a table to DB2 and SQL Prompt didn't see it. After a disable/enable cycle SQL Prompt saw it.

On the other hand it didn't make SQL Prompt refresh the cache from a linked server DB, so it is only a partial solution. It appears the only way to completely refresh the cache is to restart SSMS.

[glennforum]