Refresh Suggestions not working properly

rldawg

I upgraded to the latest version of SQL Prompt recently. If I alter a table or add a new proc or function and then do Refresh Suggestions, the new objects are not showing up in the suggestion list. I do not have the "Enable Automatic Refresh Suggestions" checked.

I am wondering if anyone else is having this issue?

Thanks

Aaron L

Just to make sure I'm testing the same thing, which version of SSMS are you using?

Is refresh suggestions not working at all or is it only under specific circumstances?
E.g. does it work in a really basic case of:

1. Create a new empty database
2. Open a new query against it with "SELECT * FROM "
3. Create a table using the table designer
4. Return to the query and refresh suggestions

Thanks,
Aaron.

rldawg

I am using SQL Prompt 6.5.0.334.

With a brand new database, I am unable to reproduce the behavior I was experiencing. I added a new table - refresh suggestions - OK. I altered the table and added a column - refresh suggestions - OK. I added a new scalar valued function - refreshed suggestions - OK

The database I was using yesterday has a considerable amount of database objects. We'll see if the results vary based on the number of objects.

It's a new day and I have relaunched SSMS and SQL Prompt. I will report back as the day goes along.

Aaron L

Thanks for the extra info - I've tried on a few larger databases but I'm still not having much luck. If you do notice it happening again it's also worth checking the log file (under SQL Prompt->Help->Show Log) just to see if there's any warnings or errors listed that might explain it.

Thanks,
Aaron.

rldawg

Thanks for investigating. I didn't do as much DB work yesterday. But what little work I did, worked fine. I will keep an eye out in the future but it may be a few days or so before I do more extensive SQL coding.

Thanks again

rldawg

Here I am again. I added new columns to a VIEW (somewhere in the middle of the column list) and the new columns did not show up in the suggestion list when I used the view in another query.

So this is what I did:

1. Added the new columns (in the middle of the column list, not at the end)
2. Refresh Suggestions -New columns did not show
3. Dropped the view
4. Refresh suggestions - Even though the view had been dropped it still shows in the suggestion list
5. Re-create the view
6. Refresh suggestions - View still shows in the suggestion list without the new columns

Based on what I see, it looks like under certain circumstances the cache of suggestions in not being refreshed properly. I have noticed that Refresh Suggestions is a lot faster than it used to be. Perhaps the process of Refresh Suggestions is skipping over some steps for the sake of speed and not updating all the necessary changes.

I really don't care that Refresh Suggestions takes a little longer for the sake of accuracy.

Thanks,
Robert

Aaron L

Hi Robert,

Were there any warnings or errors listed in the log?

It sounds like it's not refreshing the suggestions for the database at all so I'm wondering if it's refreshing the wrong database. Was the query you were using opened against the database or was it connected to another one (e.g master) with a USE statement or with a database qualifying the view?

If you'd rather clear the suggestions completely and start with a fresh cache you can disable and enable the suggestions (ctrl+shift+d twice)

Thanks,
Aaron.

rldawg

I probably sound like the boy who cried wolf.

I am sure that both query windows were pointed at the same database. The log file had nothing of note in it.

I will continue to monitor to make sure I didn't make a mistake like change a different view than the one I used in the query.

In my defense, I've used SQL Prompt for at least 7 years, and I'd say probably more like 9 years but I have no records to back that up. (I do remember using it with SQL 2000 at some point) So I am not a rookie user.

Nonetheless, I will continue monitoring and paying close attention.
Thanks,
Robert

Aaron L

No problem

I'm hoping it's just caused by some action that we're missing when we try to recreate it on our test machines. Once we can recreate it here it should be much easier to track down and fix.

KevinGreiner

I have been seeing the same behavior.

For me, I've noticed that it takes some time (10 minutes maybe?) for the suggestions to be updated. I run "refresh suggestions" on a large database over a VPN. It might take 30 seconds for the refresh to complete. But the list of suggestions isn't actually updated until later. Immediately running refresh again doesn't help.

Aaron L

Ah interesting, I wonder if the bug is the loading box isn't being shown or it's being hidden early for some reason. If the suggestions are currently loading (and taking a long time) then hitting refresh suggestions wouldn't cancel an existing load so it'd look like it's doing nothing.

Is this something that's only recently started happening?

Thanks,
Aaron.

KevinGreiner

The "loading database" box is visible and I see it progressing through processing objects, columns, parameters, indexes, script information, etc. Timing it this morning shows about 20 seconds.

I most often notice this when adding new columns to a table which I then want to immediately use as part of a SELECT or INSERT. Although I have seen it happen when creating a new stored procedure, too. However, this is not consistent. I just attempted to recreate the problem on the same server and couldn't.

Yes, I think this is new in the past few months. Sorry, I can't narrow it much further than that.

This is not a trivial database but it's not huge, either. Here are the object counts.

81 CHECK constraints
2480 Default or DEFAULT constraints
439 FOREIGN KEY constraints
3 Inlined table-functions
1934 PRIMARY KEY or UNIQUE constraints
77 Scalar functions
1444 Stored procedures
67 System tables
13 Table functions
23 Triggers
2140 User tables
318 Views

rldawg

I actually noticed something today. I was working in the wrong database this morning so I changed the database in the Database dropdown list on the toolbar. Which of course everyone knows changes the database that your query will execute on when you execute it. For example, my database is named MPTest and I switched the dropdown to MPDev. When I did refresh suggestions, it refreshed MPTest instead of refreshing the current database that the query window now points to which is MPDev.

I had to execute
Use MPDev
GO

Then when I did refresh suggestions, it refreshed the suggestions for the proper database MPDev.

Normally, switching the database dropdown list to a different database would trigger the suggestions to be refreshed to the different database.

Thanks,
Robert

AstonMartin82

Hi,

Just registered to comment on this thread... I too have been experiencing these issues, though only for the last couple of weeks and prior to installing any updates (though I did update to the latest version from the previous latest version yesterday in an attempt to fix this issue).

Changing the database using the "USE" command or for the drop down did fix my suggestions once refreshing them so thank you to whoever provided that solution on this board.

However, I have noticed that the suggestions behaviour has changed recently and prior to the solution on this board I had been disabling and re-enabling SQLPrompt via the toolbar. Generally speaking if I followed this logic my intelligence started working properly again (but only for a short period of time):

Disable Code Suggestions Enable Code Suggestions Refresh Suggestions Disable Code Suggestions Enable Code Suggestions Refresh Suggestions

Then more often than not things would work again...

It would be nice to have the experience of things working how they used to once again......

Aaron L

We've just released a new beta build with some tweaks to the loading dialog which might help out here. Could you give it a try and see if it fixes this?

Thanks!
Aaron.