Refresh Suggestions Doesn't

Provides intelligent code completion for SQL Server editors.

Moderators: David Atkinson, Anu Deshpande, Luke Jefferson

Refresh Suggestions Doesn't

Postby CodantiM » Wed Dec 09, 2009 8:29 pm

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
 
Posts: 182
Joined: Fri Jun 29, 2007 2:07 am

Postby CodantiM » Sat Dec 12, 2009 1:43 am

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.
CodantiM
 
Posts: 182
Joined: Fri Jun 29, 2007 2:07 am

Postby Brian Donahue » Thu Dec 17, 2009 5:55 pm

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
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby CodantiM » Thu Dec 17, 2009 6:07 pm

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?
CodantiM
 
Posts: 182
Joined: Fri Jun 29, 2007 2:07 am

Re:

Postby Cowski » Fri Dec 18, 2009 11:03 pm

Brian Donahue wrote: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??
Cowski
 
Posts: 15
Joined: Fri Dec 18, 2009 4:36 pm

Confirmation...

Postby Cowski » Wed Dec 30, 2009 11:04 pm

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.
Cowski
 
Posts: 15
Joined: Fri Dec 18, 2009 4:36 pm

Postby CodantiM » Mon Apr 19, 2010 10:21 pm

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
 
Posts: 182
Joined: Fri Jun 29, 2007 2:07 am

Postby CodantiM » Tue Apr 20, 2010 6:21 pm

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
 
Posts: 182
Joined: Fri Jun 29, 2007 2:07 am

Re:

Postby glennforum » Tue Jul 06, 2010 12:51 pm

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.
glennforum
 
Posts: 1
Joined: Tue Jul 06, 2010 12:47 pm


Return to SQL Prompt Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests