Red Gate forums :: View topic - Refresh Suggestions Doesn't
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt Previous Versions
SQL Prompt Previous Versions forum

Refresh Suggestions Doesn't

Search in SQL Prompt Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
CodantiM



Joined: 29 Jun 2007
Posts: 180

PostPosted: Wed Dec 09, 2009 8:29 pm    Post subject: Refresh Suggestions Doesn't Reply with quote

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?
Back to top
View user's profile Send private message
CodantiM



Joined: 29 Jun 2007
Posts: 180

PostPosted: Sat Dec 12, 2009 1:43 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6589

PostPosted: Thu Dec 17, 2009 5:55 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
CodantiM



Joined: 29 Jun 2007
Posts: 180

PostPosted: Thu Dec 17, 2009 6:07 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Cowski



Joined: 18 Dec 2009
Posts: 12

PostPosted: Fri Dec 18, 2009 11:03 pm    Post subject: Re: Reply with quote

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?? Shocked Isn't this extremely inconvenient for a program that is nearly $300??Exclamation Mad Exclamation There isn't a shortcut to do this? Or is this feature on the VERY NEAR horizon??
Back to top
View user's profile Send private message
Cowski



Joined: 18 Dec 2009
Posts: 12

PostPosted: Wed Dec 30, 2009 11:04 pm    Post subject: Confirmation... Reply with quote

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. Rolling Eyes

I hope this is fixed in a future version that my company will be able to get as a free upgrade.
Back to top
View user's profile Send private message
CodantiM



Joined: 29 Jun 2007
Posts: 180

PostPosted: Mon Apr 19, 2010 10:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
CodantiM



Joined: 29 Jun 2007
Posts: 180

PostPosted: Tue Apr 20, 2010 6:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
glennforum



Joined: 06 Jul 2010
Posts: 1

PostPosted: Tue Jul 06, 2010 12:51 pm    Post subject: Re: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group