Suggestions Not Working

MikeyCMikeyC Posts: 249 Bronze 3
If you paste this code into an SSMS window:
CREATE TABLE #FilingOrder
(
	P_ID VARCHAR(18) NOT NULL,
	C_ID NUMERIC(18,0) NOT NULL,
	F_O NUMERIC(38,0) NULL
);

CREATE UNIQUE CLUSTERED INDEX #PK_FilingOrder ON #FilingOrder(P_ID, C_ID<CURSOR>) WITH FILLFACTOR = 100;

And hit CTRL-space where the cursor is you won't get a suggestion window. (In fact you won't any where in #FilingOrder, P_ID, or C_ID in the CREATE INDEX statement.)

But if you add a SELECT statement in front of the CREATE INDEX it will start working. (In fact if you just type a space and then backspace the suggestions start working in the entire statement.)

I am using the build that fixes the CTE problem: 5.0.2.1 so that may be part of the problem.

It is almost as if SQL Prompt doesn't parse the code until you type in it. (Just loading the file, pasting the code, or deleting code doesn't work.)

Comments

  • Anu DAnu D Posts: 876 Silver 3
    Thanks for your post.

    As soon as you place a comma after the C_ID column see the code below:
    CREATE TABLE #FilingOrder
    (
       P_ID VARCHAR(18) NOT NULL,
       C_ID NUMERIC(18,0) NOT NULL,
       F_O NUMERIC(38,0) NULL
    );
    
    CREATE UNIQUE CLUSTERED INDEX #PK_FilingOrder ON #FilingOrder(P_ID, C_ID,<CURSOR>) WITH FILLFACTOR = 100;
    

    It starts suggesting the column names as till then the query is not valid.

    Let me know if this works for you.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • MikeyCMikeyC Posts: 249 Bronze 3
    It starts suggesting the column names as till then the query is not valid.

    Really, can you point me to the point in the batch that is invalid? (Adding the comma actually makes it invalid until you add a valid field name.) The batch that I posted is valid and runs fine for me as long as you take the <CURSOR> placeholder out.

    It should suggest C_ID and show the other information. For example type your comma, then delete your comma and hit CTRL-space, suggestions will come up, and yet the query is exactly how it was when you pasted it. Why does it act differently after typing than after pasting or opening a file.
  • Anu DAnu D Posts: 876 Silver 3
    Thanks for your post Michael.

    After you add the comma SQL Prompt will know that you need suggestion and you are adding columns to the query.

    In your very first post you said:

    And hit CTRL-space where the cursor is you won't get a suggestion window. (In fact you won't any where in #FilingOrder, P_ID, or C_ID in the CREATE INDEX statement.)

    But when I tried that it works for me.

    I am bit confused so I have email you a live chat link where you can show me the issue you are having. Please let em know your views on by replying my email.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
Sign In or Register to comment.