Refresh Suggestions Doesn't
MikeyC
Posts: 249 Bronze 3
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?
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
I think it would help to clarify two points:
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?
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??
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.
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.
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.