SQL Prompt is 7.3 beta (now closed)

David PriddleDavid Priddle Posts: 218
edited September 5, 2016 5:22AM in SQL Prompt
The stable release announcement can be found here.

Beta closed - thanks to everyone who took part!

This build of SQL Prompt includes:
  • Improved SQL formatting
  • Inline stored procedure (UserVoice)
  • New options to control when execution warnings are shown:
    • Statements with an INNER JOIN (UserVoice)
    • Scripts that create or alter a procedure or trigger (UserVoice)
    • TRUNCATE statements

Full release notes

Feedback
If you find any issues with this release, please post them on this forum.

If you have any suggestions for improvements, please post them on our suggestions forum so other users can vote for them.

Version history
7.3.0.768 - 30th November
  • Support ticket 78351: Exporting to excel now supports exporting money data type using German localization
  • Support ticket 78354: Script as insert now supports more localizations for date data types
  • Support ticket 78427: Fix for additional space being inserted inside subquery
  • Support ticket 78439: Fix for space being removed between PARTITION and BY
  • Support ticket 78499: Variable assignments inside SELECT statement can now be aligned like aliases
7.3.0.748 - 28th November
  • Loading indicator is now displayed when previewing a large script in the formatting options window
  • Improvements to CTE formatting options
  • OPTION clause is now placed on a new line (UserVoice and forum post)
  • Improved consistency of formatting across statements
  • Close button added to formatting options window
  • Support for multi-lined user placeholders in snippet manager using shift+enter (Forum post)
7.3.0.709 - 21st November
  • Suggestions and formatting support for new SQL Server 2016 SP1 syntax:
    • CREATE OR ALTER (UserVoice)
    • DBCC CLONEDATABASE
    • USE HINT query hint
  • Option added to change DML clause lists separately from the global list options
  • Support for Azure Active Directory integrated authentication
  • Minor changes to default formatting style
  • Improvements when formatting long IIF expressions
  • Fix for execute current statement not selecting the whole ALTER TABLE statement (Forum post)
7.3.0.681 - 16th November
  • Inline EXEC function now unwraps sp_executesql statements (UserVoice)
  • Option to align datatypes and constraints now applies to computed columns (Forum post)
  • Fix for CTE not formatting (Forum post)
  • Scrollbar is now shown on styles window if needed in high dpi mode (Forum post)
  • Improved wrapping for CASE statements exceeding the wrap column
  • Options for TOP filter now apply to DISTINCT too
  • Support ticket 77352: Fix for incorrect precision being displayed on tooltip for DATETIME2 datatypes
  • Support ticket 77487: Fix for having to having to re-activate license
7.3.0.663 - 7th November
  • Improved suggestions when using GRANT with schemas (Forum post)
  • Improved alignment of single-lined block comments
  • Support ticket 76234: Fix for incorrect column dependency being displayed
  • Support ticket 75896: Fix for incorrect column being used when expanding wildcard
7.3.0.651 - 31st October
  • SQL Prompt can now retrieve suggestions using Azure Active Directory password authentication (UserVoice)
  • Formatting off comment tag now applies to itself (forum post)
  • Fix for text being moved inside formatting off comment tags (Forum post)
  • Support ticket 76423: ambiguous columns are now always qualified with their table/alias inside an ORDER BY clause
7.3.0.642 - 27th October
  • Support for user account licensing (More info)
7.3.0.639 - 26th October
  • Fix for insert semicolons error being displayed when trying to format a script in older versions of SSMS2016 (UserVoice and Forum post)
  • Place first item on new line now works for ORDER BY in windowed functions (Forum post)
7.3.0.632 - 24th October
  • Format action added for applying column alias style (UserVoice)
  • Option added to change CREATE/ALTER parentheses separately from the global parentheses options
  • Option added to insert an empty line between JOIN clauses
  • Ole db provider names are now suggested for OPENROWSET and OPENQUERY
  • Fix for incorrectly qualifying a column using the xml "modify" method (Forum post)
  • Fix for syntax error being introduced when wrapping long lines containing multi-line strings
7.3.0.619 - 17th October
  • Fix for script not being formatted if there are errors outside of the selected text (UserVoice)
  • Whitespace at the end of the file is now maintained if "Preserve existing new lines between statements" is checked (UserVoice)
  • Statements inside TRY/CATCH blocks are now formatted the same as BEGIN/END blocks
  • Fix for parentheses alignment inside CASE expression (Forum post)
7.3.0.610 - 11th October
  • Experimental feature added to refresh the SSMS IntelliSense (UserVoice)
  • Fix for IN parentheses not being aligned correctly (Forum post)
  • Fix for parentheses in VALUES clause not being aligned (Forum post)
  • Renaming the file that a style is stored in will now rename the style
  • Support ticket 74368: Fix for a semicolon being incorrectly added on an ALTER table statement with a masked column
7.3.0.598 - 6th October
  • Formatting now indents nested JOIN clauses correctly
  • Fix for formatting issue where line breaks were removed incorrectly after multi-line comments (Forum post)
  • EXEC statements with a return value are now formatted
  • Fix for formatting issue where whitespace was added after aliases (Forum post)
  • Improved formatting for CREATE/ALTER with temporal tables
  • Improved suggestions for ALTER with temporal tables
  • Fix for issue with insert semicolons and temporal tables
7.3.0.587 - 3rd October
  • Tables used in JOIN clauses are now treated as subsequent items (Forum post)
7.3.0.585 - 30th September
  • Added new formatting options for JOINs
  • Fix for formatting failing when right aligning AS keyword in CTEs
  • Formatting no longer fails with some Azure Data Warehouse -specific statements
  • Fix formatting for negative numbers in CASE expressions (Forum post)
  • Fix for formatting failling with SELECT into variable with += (Forum post)
7.3.0.568 - 22nd September
  • Fix for formatting failing on some scripts (Forum post)
  • Option for adding spaces around parentheses contents is now used in more statements
7.3.0.564 - 20th September
  • Support for formatting scripts containing SQLCMD mode and "GO 10" syntax
  • New format action for adding/removing AS keyword for table/view alias definitions (UserVoice)
  • Simplified the parentheses "Place on new line" options
  • ALTER DATABASE statements now respect the "Indent DDL clauses" option (Forum post)
  • Column names in temp tables using quoted identifiers are now suggested
  • Inline EXEC now correctly handles parameters using the DEFAULT value
  • Fix for incorrect indentation when using UNION
  • Fix for missing space in ORDER BY between function call and DESC keyword
  • Fix for extra space being inserted between "<="
  • Fix for extra space being inserted after minus inside IN clause (Forum post)
  • Support ticket 73192: Fix for Inline EXEC not inlining negative variables correctly
  • Support ticket 73072: Fix for cancel button not responding when using "Open in Excel"
7.3.0.535 - 12th September
  • Inline EXEC feature can now inline scalar functions
  • Fix for Format SQL adding additional spaces in the middle of object names
  • Fix for Format SQL removing space between "RETURN -1;"
  • Support ticket 72842: Fix for visual glitch when using keyboard navigation on execution warning dialog
  • DBCC SHOW_STATISTICS now displays options when passing in string arguments (UserVoice)
  • 'd' is no longer used as a keyboard shortcut for deleting snippets in the snippet manager (UserVoice)
7.3.0.522 - 8th September
  • Fix for formatting options window disappearing behind SSMS when opening a drop down (Forum post)
7.3.0.513 - 7th September
  • Support ticket 72751: Fix for schema qualification being removed when auto-completing a stored procedure
7.3.0.506 - 7th September
  • Improved UI for comma options
  • Improved alignment when using the "Only tabs" option (Forum post)
  • "go" is no longer used as an auto-generated alias name (UserVoice)
7.3.0.493 - 5th September
  • Variables are now declared automatically for ouput parameters when generating an EXEC statement (UserVoice)
  • Rearranged the DML page in the formatting options
  • Minor tweaks and text changes to formatting options
  • Fix for space before IS NULL being removed when using tabs (Forum post)
  • Sp-5853: Fix for "Cannot find resource named 'System.Windows.Controls.StackPanel'. Resource names are case sensitive."
  • Changing the styles folder path no longer requires a restart of SSMS
  • Script as INSERT now outputs rowversion/timestamp columns as varbinary
7.3.0.474 - 31st August
  • SP-5911: Fix for null reference exception being thrown by the object tooltip
7.3.0.473 - 30th August
  • Empty lines between multiline comments are now handled correctly (Forum post)
  • Correct formatting for FULL OUTER JOIN (Forum post)
  • Fix for keyboard focus not changing when switching back to SSMS (Forum post)
7.3.0.460 - 25th August
  • Better handling for empty lines between statements (Forum post)
  • Handle multline comments in BEGIN / END correctly (Forum post)
  • Format SELECT ... INTO (Forum post)
  • Format opening parenthesis of INSERT ... VALUES statement (Forum post)
7.3.0.450 - 23rd August
  • Find Invalid Objects now reports objects as invalid if a connection to a linked server fails (Forum post)
  • Fix for incorrect list alignment inside a CTE statement (Forum post)
  • Declared table variables now get formatted using the same options as CREATE TABLE (Forum post)
  • New line is no longer inserted after a batch separator (Forum post)
  • Additional new lines are no longer inserted between multi-lined comments (Forum post)
7.3.0.437 - 19th August
  • Fix for error while formatting MERGE statements (Forum post)
  • Fix for formatting options window disappearing when opening a dropdown on some platforms
7.3.0.422 - 18th August
  • Support ticket 71592: Fix for wrapping long lines breaking query containing multi-lined strings
  • Support ticket 70681: Open in Excel exports Date datatypes without a time
7.3.0.418 - 18th August
  • Initial release

Comments

  • FYI: All the UserVoice links for the "And many more" list are pointing to your admin portal for UserVoice.
  • Aaron LAaron L Posts: 596 New member
    Hi dslinn,

    Thanks for letting us know! The links should be fixed now.

    Thanks,
    Aaron.
  • Blank lines being introduced in set statements inside a multi-statement table function.
    i8eRwQY.png


    Current version 7.3.0.450 (at time of post most recent. Issue currently till exists)
  • Hi Sheldon,

    We've got a fix in that should resolve this issue, we'll look into getting this released soon.

    Best regards,

    David
  • Hi Sheldon,

    We've just released this fix as part of our new build, could you download it and let us know if it solves your problem?

    Best regards,

    David
  • hafnerahafnera Posts: 16 New member
    I have a query that is over 2000 lines long and when I go to 'Format SQL' it won't run as it says "Error laying out sql" and it highlights the first letter in the first line of the SQL. However, if I run the format command on individual parts of the statement in sections, it works fine. It just doesn't work when it is run across the entire script. This did not occur before I updated to the 7.3 beta. It occurred on 7.3.450 and 7.3.460. I should also mention that the entire script can be run against SQL just fine, so there is not a syntax issue with the script.
  • hafnerahafnera Posts: 16 New member
    I love the new "Inline Stored Procedure" functionality. However, it appears it is only available when you right click on the name of the SP when you have EXEC <SP Name>. It would be nice if you could initiate this functionality any time you reference the stored procedure. So if it is proceeded by 'ALTER PROCEDURE', 'CREATE PROC', or if you right click on the stored procedure from within the SSMS tree (ie Server>Databaseses>DatabaseName>Programmability>Stored Procedures). I don't know if this is something to put on UserVoice or if it could be added as part of the existing request and included in the next Beta? Just a thought.
  • Hi hafnera,

    Would it be possible to send the script or a small script that can reproduce the formatting problem to me at david.priddle@red-gate.com?

    For the second question, would Script as Alter (F12) do what you want? Otherwise, you can hover over the stored procedure name to get the backing script.

    Best regards,

    David
  • Bryan HBryan H Posts: 4 Bronze 1
    Tabs are sometimes added after alias names. Occurrences have been noticed where the tab is added either in front or following the . operator separating the table name from the column name. Occurrences seem to happen on lengthy scripts.

    Example fragment:
    WHERE		t.	BusWeek BETWEEN @ReportStart AND @ReportEnd
    

    Example 2 fragment (Issue shows up in both lines 6 and 8 - notice the tab can be prior to or after the . operator:
    FROM	dbo.PA10702 PA_PO_ReceiptLine
    		LEFT JOIN dbo.PA01201 PA_Project_MSTR ON PA_PO_ReceiptLine.PAPROJNUMBER = PA_Project_MSTR.PAPROJNUMBER
    		LEFT JOIN dbo.PA01101 PA_Contract_MSTR ON PA_Project_MSTR.PACONTNUMBER = PA_Contract_MSTR.PACONTNUMBER
    		INNER JOIN dbo.PA01001 PA_Cost_Category_MSTR ON PA_PO_ReceiptLine.PACOSTCATID = PA_Cost_Category_MSTR.PACOSTCATID
    		LEFT JOIN dbo.PA01601 PA_ReceiptLine_Billing_Notes
    			ON	PA_PO_ReceiptLine.	PAbillnoteidx = PA_ReceiptLine_Billing_Notes.PAbillnoteidx
    		INNER JOIN DYNAMICS.dbo.MC40200 MC_Currency_SETP
    			ON	PA_PO_ReceiptLine	.PAMCCURNCYID = MC_Currency_SETP.CURNCYID;
    
  • Aaron LAaron L Posts: 596 New member
    Hi Bryan,

    Thanks for your post, we have a fix internally and should be releasing a new beta build soon.

    Our support team mentioned you opened a ticket with them to get this fixed too - please keep in mind it is still a beta version and will contain some bugs. You can switch back to the stable formatter at any time by unchecking the "Use new formatting styles" checkbox on the experimental features page.

    Sorry for the slow response - we had missed your post as it got lost in the announcement thread. I'll lock this thread to avoid it happening in the future.

    Thanks,
    Aaron.
This discussion has been closed.