Limit On SQL Statement Length?

CodantiMCodantiM Posts: 245
edited January 29, 2010 12:05PM in SQL Prompt Previous Versions
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;

Comments

Sign In or Register to comment.