No or wrong suggestions in some circumstances

MikeyC

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?)

Bart Read

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

MikeyC

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!

Bart Read

Great, glad that sorted it. I did wonder if your INSERT was a stylised representation.