I have found that I have problems suggestions not working reliably on long statements, and it doesn't appear to be a specific number of lines, but more a number of characters.
In the sample below, which is an made up query that won't run you can see what happens, if you try to get a suggestion on the left join it works unless your cursor is after the R in #CaseRate. If you move your cursor in front of the R the suggestion shows up. If you shorten the statement then the suggestions work all the way to the end. In SQL Prompt 3.x there was an option to only look at x lines, or to evaluate the whole batch, and I always had to look at the entire batch. I looked but can't find a similar option in SQL Prompt 4.
CREATE TABLE #CaseRate
(
TX_ID NUMERIC(18, 0) NOT NULL PRIMARY KEY,
Case_Rate_ID VARCHAR(18) NULL
);
WITH OrigChargeInfo (TX_ID, Version)
AS (SELECT TX_ID, 1 AS Version
FROM dbo.ECA
WHERE
Previous_TX_ID IS NULL
AND Next_TX_ID IS NOT NULL
AND DETAIL_TYPE = 1
UNION ALL
SELECT T.TX_ID, OCI.Version+1
FROM dbo.ECA T
INNER JOIN OrigChargeInfo OCI
ON T.Previous_TX_ID = OCI.TX_ID
WHERE
t.DETAIL_TYPE = 1
)
INSERT INTO msdb.dbo.backupfile
(backup_set_id,
first_family_number,
first_media_number,
filegroup_name,
page_size,
file_number,
backed_up_page_count,
file_type,
source_file_block_size,
file_size,
logical_name,
physical_drive,
physical_name,
state,
state_desc,
create_lsn,
drop_lsn,
file_guid,
read_only_lsn,
read_write_lsn,
differential_base_lsn,
differential_base_guid,
backup_size,
filegroup_guid,
is_readonly,
first_family_number,
first_media_number,
filegroup_name,
page_size,
file_number,
backed_up_page_count,
file_type,
source_file_block_size,
file_size,
logical_name,
physical_drive,
physical_name,
state,
state_desc,
create_lsn,
drop_lsn,
file_guid,
read_only_lsn,
read_write_lsn,
differential_base_lsn,
differential_base_guid,
backup_size,
filegroup_guid,
is_readonly,
first_family_number,
first_media_number,
filegroup_name,
page_size,
file_number,
backed_up_page_count,
file_type,
source_file_block_size,
file_size,
logical_name,
physical_drive,
physical_name,
state,
state_desc,
create_lsn,
drop_lsn,
file_guid,
read_only_lsn,
read_write_lsn,
differential_base_lsn,
differential_base_guid,
backup_size,
filegroup_guid,
is_readonly,
first_family_number,
first_media_number,
filegroup_name,
page_size,
file_number,
backed_up_page_count,
file_type,
source_file_block_size,
file_size,
logical_name,
physical_drive,
physical_name,
state,
state_desc,
create_lsn,
drop_lsn,
file_guid,
read_only_lsn,
read_write_lsn,
differential_base_lsn,
differential_base_guid,
backup_size,
filegroup_guid,
is_readonly,
first_family_number,
first_media_number,
filegroup_name,
page_size,
file_number,
backed_up_page_count,
file_type,
source_file_block_size,
file_size,
logical_name,
physical_drive,
physical_name,
state,
state_desc,
create_lsn,
drop_lsn,
file_guid,
read_only_lsn,
read_write_lsn,
differential_base_lsn,
differential_base_guid,
backup_size,
filegroup_guid,
is_readonly,
first_family_number,
first_media_number,
filegroup_name,
page_size,
file_number,
backed_up_page_count,
file_type,
source_file_block_size,
file_size,
logical_name,
physical_drive,
physical_name,
state,
state_desc,
create_lsn,
drop_lsn,
file_guid,
read_only_lsn,
read_write_lsn,
differential_base_lsn,
differential_base_guid,
backup_size,
filegroup_guid,
is_readonly,
filegroup_guid,
is_readonly,
LASTLineIsTheEndOfIt
) SELECT
*
FROM msdb.dbo.backupfile T
LEFT JOIN #CaseRate CR
on T.TX_ID = CR.TX_ID;
I have observed the same thing on long statements.
In the sample below, which is an made up query that won't run you can see what happens, if you try to get a suggestion on the left join it works unless your cursor is after the R in #CaseRate. If you move your cursor in front of the R the suggestion shows up. If you shorten the statement then the suggestions work all the way to the end. In SQL Prompt 3.x there was an option to only look at x lines, or to evaluate the whole batch, and I always had to look at the entire batch. I looked but can't find a similar option in SQL Prompt 4.