Issue running current query in Alter Table statement

tonywaddle

SQL Prompt doesn't seem to like the following statement:

ALTER TABLE x.y
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START
HIDDEN DEFAULT GETUTCDATE(),
EndTime DATETIME2 GENERATED ALWAYS AS ROW END
HIDDEN DEFAULT
CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

It leaves out the closing bracket resulting in invalid syntax error.

Thanks,
Tony

Aaron L

Hi Tony,

Thanks for your post! I can reproduce this here and will look into getting it fixed now.

Thanks,
Aaron.

tonywaddle

Thanks for the quick response Aaron.

There's another issue that may be related. If I highlight a long SQL statement that exists on one line (i.e. dynamic SQL string) only a portion of the SQL statement will remain highlighted after applying SQL Prompt's formatting.

For instance the output of the query below is 100 SQL statements in one long string (no line breaks). Formatting this results in 100 lines of code, however after formatting I only have halfway through line 86 selected. I think the intended behaviour would be for the entire statement to remain selected.

 DECLARE @DynSQL VARCHAR(MAX);
 SELECT @DynSQL = ISNULL(@DynSQL + ';', '')
        + 'select 123'
 FROM   (SELECT TOP 100 *
         FROM   sys.objects        
        ) a;

 SELECT @DynSQL;

Thanks,
Tony

Aaron L

Hi Tony,

The issue with ALTER TABLE not being fully selected when running execute current statement is now fixed in the latest 7.3 beta build which can be downloaded here.

I think the new formatting system in the 7.3 beta might have also fixed the second issue you were seeing. Although if you'd prefer to continue to use the stable formatter for now you can disable the new system on the experimental features page of the options.

Thanks,
Aaron.

tonywaddle

Thanks Aaron - the new formatting system seems to have fixed both issues, but I've noticed a slightly different issue with it.

If a SQL statement has X number of spaces before it, for instance: " select 123", highlighting the SQL statement and applying the formatting will result in the text being highlighted X number of characters in.
So if there are three spaces before the statement, I will only have "ect 123" highlighted after applying the formatting. The same is true if there is tab indentation at the start of the string, but each tab will result in one character not highlighted.

This is only related to formatting highlighted text, and only occurs if you don't also highlight the leading spaces. In my scenario I use the end / shift-home shortcuts to highlight the text so the leading spaces are not highlighted. Not a big issue - obviously I can just press shift-home twice to get the complete line, just wanted to bring it to your attention.

Thanks,
Tony