SQL Prompt "Forgetting" Some Members of Candidate List

jader201

I've been using SQL Prompt for several months, and one issue I (and my colleagues) keep having is that it will lose certain members from the candidate list, forcing us to manually use "Refresh Cache" in order to restore them.

I can't figure out any rhyme or reason as to why or when it does this, except that it seems to be tables we frequently work with/query. But basically, the table will just suddenly stop showing up in the list, or the table will show up but the columns will not.

In some cases, this can be due to something screwy w/ the query that just causes issues w/ SQL Prompt, which is expected. But I'm referring to when there is no issue w/ the query, and it is able to access some tables but not others. And refreshing the cache (without changing the query) *always* fixes it.

And as I said, it's not just me, but all of my coworkers have reported having this issue. And obviously, those of us the use the tool more often report more issues than the others.

Any ideas what the cause is, and if there is a fix (other than constantly refreshing the cache)?

System Info:
SQL Prompt version 3.9.0.43
SQL Server 2005, SP2 (build 3790)
Windows XP SP3

Thanks in advance.

Brian Donahue

Hi Jerad,

I'm sorry, but I don't know what the problem could be. SQL Prompt keeps a copy of the database schema cached on disk, so it seems unlikely that tables would just drop out until the cache is refreshed.

I'll ask someone else to see if they know of anything that would cause this.

jader201

Any updates on this? We're still having the issue.

If there's anyway I can help troubleshoot, let me know. For example, if it would help to send you a cache file before (when the members are missing) and after (when I do a refresh and restore them), let me know.

Thanks.

George Palacean

I have the same problem

wsoranno

I am having samilar problem.
I have determined that SQL Prompt does not function properly in an Update statement that has a From clause.

If I try to do the following:

Update Person
set ExpirationDate = a
From Person as p
join OpData.dbo.ST_PROG as sp
on p.TechId = sp.TECH_ID

The prompting works fine where I would expect it to.
When I add a second join:

Update Person
set ExpirationDate = a
From Person as p
join OpData.dbo.ST_PROG as sp
on p.TechId = sp.TECH_ID
join OpData.dbo.

The candidate list has the columns for the table Person.
When I add any additional letters the list goes blank.
Refreshing the cach does not update the list.

If I do the following:

--UPDATE Person
--SET ExpirationDate = a
SELECT *
FROM Person AS p
JOIN OpData.dbo.ST_PROG AS sp
ON p.TechId = sp.TECH_ID
JOIN OpData.dbo.

Then I get the list of tables and views from OpData.

I have this problem in SSMS 2008 and VSTS 2008.

I hope this information helps to isolate the problem and solve it.

Microsoft SQL Server Management Studio 10.0.1600.22
Operating System Vista Enterprise 64bit SP1
Microsoft Visual Studio Team System 2008 Database Edition GDR Version 9.1.31124.01

jader201

In my case, it has nothing to do with the select query. I understand that SQL prompt has issues parsing some certain queries and formats, and I usually know how to "workaround" these by changing the structure of my query.

In my case, it is simply forgetting tables and columns. I can even clear my query window, write a simple "select * from TableName", and the table won't even come up in the list (even though almost all other tables do show up). I can then do a "Refresh Cache", and it will then show up in the candidates list. This workaround fixes the issue 100% of the time, and this happens often with my colleagues as well, so it's not just me.

tanya

Hello,

Apologies for the delayed response.
We have identified a possible reason for this issue and have addressed it in SQL Prompt v4 which is currently under development.
An early access of this release should be available end of May. I will keep the forum posted with the release updates.

Thanks for your patience and for reporting the issue.

Regards,
Tanya
Project Manager
Red Gate Software Ltd