Refresh Suggestions Doesn't

CodantiMCodantiM Posts: 245
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?

Comments

  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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?
  • 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.

    So if I'm reading this correctly, your saying that if a object is created, or MANY objects are created on another server & I'm running linked servers, I have to exit all the way out of SSMS and log back into SSMS in order for the dropdown to refresh?? :shock: Isn't this extremely inconvenient for a program that is nearly $300??:!: :x :!: There isn't a shortcut to do this? Or is this feature on the VERY NEAR horizon??
  • 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. :roll:

    I hope this is fixed in a future version that my company will be able to get as a free upgrade.
  • 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.
  • 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.
  • CodantiM wrote:
    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.

    Seriously this is incredibly poor design. When you are developing a database you are constantly making changes and the fact there is no quick way to refresh ALL of the cache for SQL Prompt is really absurd.

    I had to shut off SQL Prompt is just has become too annoying not having the information refreshed you have to wonder if RedGate even uses their products - this limitation becomes apparent in the first 5 minutes of use.

    Overall for the money you pay for Redgate products you would expect not to have such a glaring limitation in one of their products.

    Makes me just want to look for another vendor to spend my hard earned money with.
Sign In or Register to comment.