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

No or wrong suggestions in some circumstances

MikeyCMikeyC Posts: 249 Bronze 3
edited September 12, 2007 11:45AM in SQL Prompt Previous Versions
I have some code that must confuse SQL Prompt 3.6 EA:

INSERT INTO dbo.edic_cga_details
SELECT
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.tdl_id,
t.<cursor>
FROM edic.dbo.edic_cga_details_Step1 t
WHERE t.<cursor>;

When I type the . right before where either <cursor> is above I get the pop up that lists table names instead of fields from the table aliased as "t". In some case it pops up and says "no suggestions" but if I change much of anything in the code, like removing a line with a CASE statement in it, or even just deleting a few lines of the field selections it works. (I couldn't narrow it down to one thing that makes it work/not work.) I couldn't quickly come up with a sanitized SQL statement that brought up the "no suggestions" box.

In some case where there is a lot of fields listed out in the select portion of an insert into statement it doesn't recognize table aliases, and brings up a list of tables when I type "t."

Most of the cases I had problems with are large "INSERT INTO () SELECT" statements, and it isn't only a problem in the select portion, I have problems in the where portion as well.

Is there any way to turn off snippet insertion all together? I have had cases where I have a table aliased as "ct" and when I type "ct." it inserts the "Create Table" snippet instead of bringing up suggestions. (Maybe aliases should take precedence over snippets?)

Comments

  • Options
    Bart ReadBart Read Posts: 997 Silver 1
    OK, that's a very odd INSERT statement... but that aside, if your statements are typically long you may run into SQL Prompt's hard limit for the amount of SQL it will parse, and this can lead to you getting no useful auto-complete suggestions. I say "hard" in the sense that SQL Prompt won't go beyond it, however you can change the value.

    1) Open the Options dialog by clicking SQL Prompt > Options on the main menu.
    2) Go to the Listed Candidates > Variables and Parameters screen. Now either (a) increase the maximum number of lines, or (b) select "Search entire batch/GO block..."

    Then you're good to go. Note that if you work with large scripts with very large SQL batches going for option (b) above may well degrade performance. I'd suggest (a) and in the initial case increase the value to 100 or 150.

    Hope that helps.


    Thanks,
    Bart
    Bart Read
    Principal Consultant
    bartread.com Ltd
  • Options
    MikeyCMikeyC Posts: 249 Bronze 3
    Yeah, that wasn't the real INSERT statement, but it was a sanitized one that I could share.

    Changing that options appears to have solved the problem, though I change it to 250, since we deal with a lot of large statements.

    Thanks!
  • Options
    Great, glad that sorted it. I did wonder if your INSERT was a stylised representation. :)
    Bart Read
    Principal Consultant
    bartread.com Ltd
Sign In or Register to comment.