Limit On SQL Statement Length?

MikeyC

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;

Matthew.Wilkerson

I have observed the same thing on long statements.

Anu D

Many thanks for your post.

Couple of customer have raised this issue hence i have logged this issue in our internal tracking system whose unique id is SP-2812.

Our development team is looking into it. I will update you as soon as I get any details on it's progress.

Sorry about the inconvenience caused by this issue and thanks for your patience.

MikeyC

Is there any progress on this issue? I just installed 4.0.3 and it actually made it worse. Now it appears that SQL Prompt is looking at even fewer characters/lines for suggestions.

Any chance of getting the "Evaluate the whole batch" option back like 3.x had? I don't care if it is a little slower, slower is better than useless.

Anu D

Hello All,

If you need a workaround for the issue can you kindly email support@red-gate.com and I will email it to you?

Refer: http://www.red-gate.com/MessageBoard/vi ... hp?t=10244

MikeyC

As a note for people, if you get their instructions and follow them you can increase how much of your batch gets processed, but it is still slower than 3.x looking at the entire batch.