Stunned! "No Candidates"

PDinCA

Cache refreshed.
New Query Window.
Typed:

SELECT * FROM dbo.Country c WHERE c.

and "No Candidates" either offered or Ctrl+Space requested.

How can such a simple statement be so simply ignored?

Version 3.6.0.109

Chris Auckland

Thanks for your post.

I think this issues to more to do with getting used to the way SQL Prompt works, and how it tries to help you write the SQL Code with fewer keystrokes.

When you get the the WHERE part of the clause, SQL Prompt already knows which table you are referencing, so there is not need to qualify the table name with the alias first. After you type WHERE<space> you will be prompted with the correct columns for the table. On completion it will insert the selected column e.g. WHERE id

You can configure SQL Prompt to automatically qualify the column name on completion so that when you choose id from the list it will be inserted as c.id by enabling the option: SQL Prompt > Options > Inserted Candidates > Formatting > Qualify Column Name

I hope this helps explain the situation.

Let me know if you need more information.

PDinCA

Actually it's nothing to do with "getting used to how it works" - I've been using SQL Prompt since Red-Gate first released beta versions at 2.0...

It has more to do with SQL Prompt getting confused and "going to sleep on the job". Sometimes the query I posted will be supported by SQL Prompt giving me a candidate list, like just now, but other times absolutely no candidate popup appears either after the WHERE or after the c., even when I provoke SQL Prompt with a Ctrl+Space.

In your response you say that there is no need to qualify the table name - agreed. So why does the candidate list contain it? Probably because many of us have standards that require us to qualify every column with a table alias... and we may well augment the query down the line with a JOIN or a correlated subquery, so we're well advised to supply and use an alias...

The essence of my post is that "With alarming frequency, SQL Prompt 3.6.0.109 stops recognizing simple SQL statements for auto-complete." Also alarming is the fact that it will also say, in effect, "no comment" when adding JOIN criteria, neither does it correctly scan scripts for new @variable DECLARE's despite the scan range being 75 lines and the new variable (a TABLE variable) being defined 20 lines above the carat. It ain't perfect; it's frequently better than nothing; sometimes it really gets in the way, as with IS NOT NULL provoking a candidate list of NULLIF that I must escape (delay is 500 but it still insists on getting in my way!) I can only hope that 3.7 will improve on these not-very-complex failures.

Tom Harris

Hello,

All the developers are out attending Tech Ed in Barcelona this week. I will find someone to investigate further once we are back in the office on Monday.

Please accept my apologies,

Tom Harris
Head of Development
Red Gate Software

borki

Hope the Developers had a great time barcelone and are back on deck....
Well I am having similar issues to the previous poster. Here is my story:

Environment:
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System: WinXP 5.1.2600 (SP2)
SQLPrompt 3.6.0.109
Refreshed the cache!!

Having lots of issues with the suggest list not displaying the column names. Out of curiosity I opened a new SQL window, and the candidate list comes up with the correct suggestions. I then started to paste the the select statement into various places of my longish (600+ lines) SQL script. I eventually found the place where it stops working. I attach 2 images, the first one showing the test statement before things go pearshape, then again after a harmless sql statement, when auto suggest does no longer work.





What is happening here?

Curious minds want to know...