What are the challenges you face when working across database platforms? Take the survey

Stunned! "No Candidates"

PDinCAPDinCA Posts: 642 Silver 1
edited November 20, 2007 5:26AM in SQL Prompt Previous Versions
Cache refreshed.
New Query Window.
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?

Jesus Christ: Lunatic, liar or Lord?
Decide wisely...


  • Options
    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.
  • Options
    PDinCAPDinCA Posts: 642 Silver 1
    edited November 9, 2007 11:54AM
    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 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.
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Options

    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
  • Options
    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:

    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)
    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...
    Felix Burkhard
Sign In or Register to comment.