SQL Prompt 6.5 beta (Now closed)

Aaron LAaron L Posts: 596 New member
edited March 19, 2015 11:25AM in SQL Prompt
The stable release announcement can be found here.

Beta closed - thanks to everyone who took part!
  • Tab coloring improvements (UserVoice and experimental feature feedback)
  • GROUP BY and ORDER BY suggestions improvements (UserVoice, UserVoice and UserVoice)
  • Import/export all options (UserVoice)
  • SQLCMD mode support (UserVoice and UserVoice)
  • Improvements to closing parentheses behavior (UserVoice)
  • Format actions (Forum post)
  • Full suggestions for cross-database queries (Forum post)
  • Temporary tables and their columns can now be renamed with F2
  • Useful keywords are promoted to the top of the suggestions. For example, SELECT is shown above SAVE
  • "Add brackets to all identifiers" format action (UserVoice)
  • "Close All With No Changes" context menu item on tabs
  • Azure v12 suggestions

Version history
6.5.0.304 - 24th March
  • The caret is now placed on the first value after auto-completing an INSERT statement (Forum post)
6.5.0.291 - 19th Match
  • New syntax is now suggested when connecting to Azure v12 databases
  • Encapsulate as new stored procedure will now use the entire script if no selection is made (Forum post)
6.5.0.271 - 12th Match
  • Fix for find unused variables with OUTPUT clause (Forum post)
6.5.0.264 - 5th March
  • "Add brackets to all identifiers" format action added (UserVoice)
  • "Close All With No Changes" context menu item added on tabs
  • Tab coloring servers and databases now accept wildcard matches
  • Remove square brackets will now insert a space if removing the bracket would result in merging two identifiers together
  • SOC-6568: Tab history will no longer reopen migration scripts which have been closed by SQL Source Control
6.5.0.255 - 2nd March
  • Fix for unnecessary qualification being inserted when running qualify object names (Forum post)
6.5.0.254 - 27th February
  • Qualify object names no longer qualifies with the wrong owner on an INSERT's WHERE clause (Forum post)
  • BEGIN/END highlighting now works with scripts containing BEGIN DIALOG (Forum post)
6.5.0.243 - 25th February
  • Fix for using qualify object names with subqueries (Forum post)
  • Fix for duplicate semicolon being inserted (Forum post)
6.5.0.234 - 24th February
  • Errors outside the selected text are no longer reported when using insert semicolons (Forum post)
  • Individual columns from expressions are no longer included in "All non-aggregated columns" (Forum post)
  • GROUP BY and HAVING now recognise CLR aggregates
  • SP-5020: Fix for null reference when using select in object explorer (Ctrl+F12) on some localizations of SSMS
6.5.0.208 - 20th February
  • "All non-aggregated columns" no longer includes constant expressions (Forum post)
  • Fix for variable being marked as unused inside a CATCH block (Forum post)
6.5.0.205 - 20th February
  • Fix for computed columns in ORDER BY statement being deleted during a format
6.5.0.199 - 18th February
  • Temporary tables in strings can now be renamed by pressing F2 (Forum post)
  • Fixes for renaming multiple SQL CMD variables or temporary tables on a single line
6.5.0.193 - 17th February
  • Rename for temporary tables now renames in strings and comments (Forum post)
  • Fix for renaming temporary tables before their creation (Forum post)
6.5.0.192 - 17th February
  • "All non-aggregated columns" now expands correctly with alternative column alias style (Forum post)
  • Fix for alias being incorrectly qualified if it has the same name as a table (Forum post)
  • Text selection's direction is maintained after a format (Forum post)
  • Fix for "position" being incorrectly uppercased in some contexts
  • Removed duplicate Prompt menu items when both SSMS2012 and SSMS2014 are installed
6.5.0.183 - 11th February
  • The "All non-aggregated columns" suggestion now uses the column qualification options
  • Improved filtering for suggestions after qualifying with a database
6.5.0.179 - 11th February
  • SP-5335: Fix for InvalidCastException with GROUP BY clause
6.5.0.177 - 10th February
  • Initial release

Feedback
If you have any problems 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.
«1

Comments

  • Some feedback based on 1 day of use.

    Tab coloring - I really like the extra stripe of color at the bottom of the screen. For some reason, this is more noticeable to me than the tab color at the top.

    Having "insert semicolons" on by default was unexpected. I found the option easily enough to turn it off. Why is it on by default?
  • Aaron LAaron L Posts: 596 New member
    Hi Kevin,

    Thanks for the feedback!

    We made Format SQL insert semicolons by default as they are recommended by Microsoft (they'll be required in a future version of SQL Server) and they shouldn't affect the way the script executes so we expected that most people would want them inserted. Out of curiosity, why don't you want semicolons inserted?

    Thanks,
    Aaron.
  • It's not that I'm opposed to semicolons in SQL. It's just that I've never written SQL with semicolons, except for when it's been required by a CTE or DDL.

    Because I work on a team of developers, we'll all need to agree. Otherwise, we'll end up flipping back and forth.

    One more thing. I noticed there is text at the top of SQL Prompt - Options - Format > Actions: "When you run Format SQL (Ctrl+K, Ctrl+Y), SQL Prompt will:". Does this text change if the keystroke is changed? Would that be hard? My keystroke is Ctrl+B, Ctrl+L, I'm not sure why.
  • Aaron LAaron L Posts: 596 New member
    Hi Kevin,

    Hopefully it's not too much of a pain to turn off the option if you do decide not to use semicolons across your team? I know it's a change of behavior from what we had before which is annoying but we think most of our users will prefer it on as the default (especially if semicolons do become mandatory)

    I think that both Ctrl+K, Ctrl+Y and Ctrl+B, Ctrl+L are bound to the Format SQL command (I'm not sure what the story was behind having both of them, it goes back before I joined the team or our current version control history stretches). At the moment that label is just static text but I'll see if we can include both of the shortcuts.

    Thanks,
    Aaron.
  • KevinGreinerKevinGreiner Posts: 37 Bronze 3
    edited February 13, 2015 12:31PM
    I found a case where SQL Prompt Format SQL attempts to qualify a table name when it shouldn't.

    Here is the SQL script to create objects required:
    CREATE SCHEMA aa;
    GO
    CREATE SCHEMA bb;
    GO
    CREATE TABLE aa.Apples (Species VARCHAR(20) NOT NULL);
    CREATE TABLE bb.ap (apnme VARCHAR(20) NOT NULL);
    DELETE ap
    FROM aa.Apples ap
    

    Now run Format SQL. I realize the DELETE statement is somewhat contrived. Assume there is a more complex JOIN required.

    The last statement becomes this.
    DELETE  bb.ap
    FROM    aa.Apples ap;
    
  • bstrautinbstrautin Posts: 8 Bronze 2
    edited February 16, 2015 10:25AM
    Column aliases in the form "x = [column name]" break the "All non-aggregated columns" group by functionality.

    For example, if you type this, and pick "All non-aggregated columns", you get "=" instead of "o.type_desc":
    select t = o.type_desc, count(*)
    from sys.objects o
    group by 
    
  • Aaron LAaron L Posts: 596 New member
    Hi Kevin and bstrautin,

    Thank you for the bug reports! we can recreate both locally and we're looking into fixes now.

    Thanks!
    Aaron.
  • sdkssdks Posts: 44 Bronze 2
    Small bug in formatting:
    Open multistatement query that does not fit to 1 screen. Go to the top query and select it from the bottom to the top. Press format query. The selected query jumps to the top of the screen. I think that the query is supposed to stay on the same window position.
  • Hi

    I am trying to use F2 to rename # tables. If I have the following code:
    IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable
    
    SELECT * 
    INTO #TestTable
    FROM dbo.tableName tn
    
    SELECT * 
    FROM #TestTable AS tt
    

    and put the cursor in the table name on line 4 or 8, hit F2 and rename the table then the two #TestTable in line 1 do not get renamed.

    If i have the following code:
    SELECT * 
    INTO #TestTable
    FROM dbo.tableName tn
    
    SELECT * 
    FROM #TestTable AS tt
    
    IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable
    

    and repeat the rename then the first #TestTable in final line doesn't get renamed.

    Regards,

    Matt
    Matt
  • Hi Matt,

    Thanks for finding this! We can reproduce this and we're looking into a fix for it now.

    Best regards,

    David
  • Aaron LAaron L Posts: 596 New member
    Hi Kevin, bstrautin and sdka,

    We've just shipped a new beta build (6.5.0.192) which should have fixes for the issues you mentioned. If you're still having problems with that version please do let us know!

    Thanks,
    Aaron.
  • Hi Matt,

    We've just released a build of SQL Prompt that has the fixes to renaming temporary tables you raised. Let us know if you have any more issues!

    Best regards,

    David
  • Hi

    It's still not working as it should I'm afraid. 2 issues - 1 minor, 1 not so minor!

    Using this code again:
    IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable
    
    SELECT * 
    INTO #TestTable
    FROM dbo.tableName tn
    
    SELECT * 
    FROM #TestTable AS tt
    

    The minor issue: if you put your cursor in the first #TestTable (the one between the single quotes) and hit F2 it doesn't highlight the table name as it does if you do the same thing in any of the other instances of #TestTable. I guess it should?
    The other issue is:
    1) put the cursor in #TestTable and hit F2
    2) change the table name making it longer
    3) the second #TestTable on line one stays in the same place (Col59), but the text before it shifts right (because the length of the first #TestTable has increases)
    4) because of this the characters to the left of the second #TestTable are deleted.

    Hopefully that description makes sense...let me know if it doesn't!

    Cheers

    Matt
    Matt
  • sdkssdks Posts: 44 Bronze 2
    another bug. Automatic semicolon insert does not insert it before SEND ON CONVERSATION. Example:
    IF ( 1 = 1 )
                        BEGIN
                            SEND ON CONVERSATION @conversation_handle MESSAGE TYPE @ResultMessageTypeName ('test');
                        END;
    

    Expected result:
    IF ( 1 = 1 )
                        BEGIN;
                            SEND ON CONVERSATION @conversation_handle MESSAGE TYPE @ResultMessageTypeName ('test');
                        END;
    
  • Hi Matt,

    We've fixed the bug relating to rename not working with multiple instances on the same line, and also you are able to use F2 to rename temp table names inside strings and comments.

    Please let us know if you find anything else.

    Best regards,

    David
  • Aaron LAaron L Posts: 596 New member
    Hi sdks,

    I think this is the behavior we'd expect to see here. We don't place semicolons after the BEGIN keyword as semicolons are meant for terminating statements so we felt it was a bit strange to terminate a BEGIN…END block at the BEGIN. This is also the style Microsoft use in their examples e.g the example here.

    While SEND ON CONVERSATION does require the previous statement to be terminated with a semicolon (which we would insert for you) I don't think it requires one after the BEGIN?

    Thanks,
    Aaron.
  • sdkssdks Posts: 44 Bronze 2
    Hi Aaron. Semicolon before SEND is exactly what I meant.
    Aaron Law wrote:
    Hi sdks,

    I think this is the behavior we'd expect to see here. We don't place semicolons after the BEGIN keyword as semicolons are meant for terminating statements so we felt it was a bit strange to terminate a BEGIN…END block at the BEGIN. This is also the style Microsoft use in their examples e.g the example here.

    While SEND ON CONVERSATION does require the previous statement to be terminated with a semicolon (which we would insert for you) I don't think it requires one after the BEGIN?

    Thanks,
    Aaron.
  • Hi

    I've been using the new GROUP BY "All non-aggregated columns" feature. When there are constant values in the SELECT these get included in the GROUP BY.
    For example this code is generated using this feature:
    SELECT 0 AS NewIdField,
           t.FName ,
           SUM(t.Volume)
    FROM #test t
    GROUP BY 0 ,
             t.FName
    

    If I run that I will get the following error:

    Msg 164, Level 15, State 1, Line 5
    Each GROUP BY expression must contain at least one column that is not an outer reference.
    Matt
  • Hi Matt,

    Thanks for the report, please keep them coming! We've got this reproduced and have a fix in place. We'll spend a bit more time checking for other cases like this and then release a bug fix.

    Best regards,

    David
  • Hi Matt,

    We've included the fix in the latest download. Please let us know if you have any issues.

    Best regards,

    David
  • Hi

    There's a slight issue with Format SQL when highlighting a section of code and there is a syntax error in non-highlighted code.

    Using this code as an example:
    SELECT Id, COUNT(DISTINCT t.FName) AS CountFirstName
    INTO #Test2
    FROM #Test AS t
    GROUP BY t.Id
    
    SELECT 
    FROM #Test AS t
    
    SELECT * 
    FROM #Test2 AS t
    ORDER BY 1,2
    

    If I highlight the first SELECT and hit Ctrl + K + Y I get the following error:


    SQL Prompt - Inserting semicolons...
    SQL Prompt was unable to complete this operation.

    Problem areas have been highlighted.
    OK


    and the comma on the last line between 1,2 (non-highlighted code) is underlined in red. The highlighted code is successfully formatted though. The cause seems to be the syntax error in the second SELECT (the lack of SELECT columns or *).

    Regards,

    Matt
    Matt
  • Another one for you....

    With GROUP BY "All non-aggregated columns" I have an issue when combining columns in the SELECT. Probably easiest to explain with an example.

    If I have the following SELECT:
    SELECT t.val1 + t.Val2 AS Total, COUNT(DISTINCT code)
    FROM #test t
    

    and then add the GROUP BY and choose All non-aggregated columns I get the following code:
    SELECT t.val1 + t.Val2 AS Total, COUNT(DISTINCT code)
    FROM #test t
    GROUP BY t.val1 + t.Val2 ,
             t.val1 ,
             t.Val2
    

    where in fact I want this:
    SELECT t.val1 + t.Val2 AS Total, COUNT(DISTINCT code)
    FROM #test t
    GROUP BY t.val1 + t.Val2
    

    Cheers

    Matt
    Matt
  • Hi Matt,

    We've fixed this in the latest release. We've also improved the GROUP BY clause so CLR aggregates should be recognised as well.

    Let us know if you find any more issues!

    Best regards,

    David
  • Hello, I'm running the version, 6.5.0.234 - 24th February.
    I'm on SQL 2012, latest SP/CU.

    Doing the, "Qualify object names" produced unexpected results in the joins after the, "HAVING" clause... basically it should join the preCheckout to the tmp table, not tmp to tmp.
    I'm positive there is a more graceful way to achieve the results, but nevertheless.

    BEFORE:
    SELECT COUNT(preCheckout.EmpPersonID) countemppersonid
    FROM   preCheckout
    WHERE  preCheckout.CheckoutDate IN (SELECT CheckoutDate
                                       FROM   preCheckout AS tmp
                                       GROUP BY CheckoutDate,
                                              EmpPersonID,
                                              StoreNum,
                                              Period,
                                              EmpPosition
                                       HAVING COUNT(*) > 1
                                              AND EmpPersonID = preCheckout.EmpPersonID
                                              AND StoreNum = preCheckout.StoreNum
                                              AND Period = preCheckout.Period
                                              AND EmpPosition = preCheckout.EmpPosition)
          AND preCheckout.StoreNum = @StoreNum
          AND preCheckout.CheckoutDate = @Date
          AND preCheckout.Period = @Period
    

    AFTER:
    SELECT COUNT(preCheckout.EmpPersonID) countemppersonid
    FROM   preCheckout
    WHERE  preCheckout.CheckoutDate IN (SELECT tmp.CheckoutDate
                                       FROM   preCheckout AS tmp
                                       GROUP BY tmp.CheckoutDate,
                                              tmp.EmpPersonID,
                                              tmp.StoreNum,
                                              tmp.Period,
                                              tmp.EmpPosition
                                       HAVING COUNT(*) > 1
                                              AND tmp.EmpPersonID = tmp.EmpPersonID
                                              AND tmp.StoreNum = tmp.StoreNum
                                              AND tmp.Period = tmp.Period
                                              AND tmp.EmpPosition = tmp.EmpPosition)
          AND preCheckout.StoreNum = @StoreNum
          AND preCheckout.CheckoutDate = @Date
          AND preCheckout.Period = @Period 
    

    Best,

    John
  • Hi John,

    Thanks for finding this! We've put a fix for this in the latest version of the beta. Let us know if this solves your problem.

    Best regards,

    David
  • Using 6.5.0.243, SQL 2012, latest SP/CU.

    The apply qualifying names chooses the wrong table.
    Look at the where clause after SQL Prompt applies changes... it should still be using table, precheckout.


    PREVIOUS:
        IF @ErrCount = 0
            BEGIN
                INSERT  INTO Checkout
                        (CheckoutDate,
                         EmpStoreNum,
                         EmpLastName,
                         EmpFirstName,
                         EmpPersonID,
                         EmpPosition,
                         StoreNum,
                         Period,
                         TransferredTips,
                         NonCashTips,
                         GiftCards,
                         Checks,
                         CashDue,
                         TipsharePaid,
                         FoodSales,
                         TotalReceipts,
                         ServiceCharge,
                         TipshareSalesPercent,
                         LogonID
                        )
                SELECT  CheckoutDate,
                        EmpStoreNum,
                        EmpLastName,
                        EmpFirstName,
                        EmpPersonID,
                        EmpPosition,
                        StoreNum,
                        Period,
                        TransferredTips,
                        NonCashTips,
                        GiftCards,
                        Checks,
                        CashDue,
                        TipsharePaid,
                        FoodSales,
                        TotalReceipts,
                        ServiceCharge,
                        TipshareSalesPercent,
                        LogonID
                FROM    preCheckout
                WHERE   StoreNum = @StoreNum
                        AND CheckoutDate = @Date
                        AND Period = @Period;
    	
                SET @ErrCount = @@ERROR;
            END;
    

    AFTER QUALIFY OBJECT NAMES:
        IF @ErrCount = 0
            BEGIN
                INSERT  INTO Checkout
                        (CheckoutDate,
                         EmpStoreNum,
                         EmpLastName,
                         EmpFirstName,
                         EmpPersonID,
                         EmpPosition,
                         StoreNum,
                         Period,
                         TransferredTips,
                         NonCashTips,
                         GiftCards,
                         Checks,
                         CashDue,
                         TipsharePaid,
                         FoodSales,
                         TotalReceipts,
                         ServiceCharge,
                         TipshareSalesPercent,
                         LogonID
                        )
                SELECT  preCheckout.CheckoutDate,
                        preCheckout.EmpStoreNum,
                        preCheckout.EmpLastName,
                        preCheckout.EmpFirstName,
                        preCheckout.EmpPersonID,
                        preCheckout.EmpPosition,
                        preCheckout.StoreNum,
                        preCheckout.Period,
                        preCheckout.TransferredTips,
                        preCheckout.NonCashTips,
                        preCheckout.GiftCards,
                        preCheckout.Checks,
                        preCheckout.CashDue,
                        preCheckout.TipsharePaid,
                        preCheckout.FoodSales,
                        preCheckout.TotalReceipts,
                        preCheckout.ServiceCharge,
                        preCheckout.TipshareSalesPercent,
                        preCheckout.LogonID
                FROM    preCheckout
                WHERE   Checkout.StoreNum = @StoreNum
                        AND Checkout.CheckoutDate = @Date
                        AND Checkout.Period = @Period;
    	
                SET @ErrCount = @@ERROR;
            END;
    
  • When trying to run the command CTRL + B, CTRL + E to Encapsulate as New Stored Procedure in SSMS 2014, and SQL Prompt Build 6.5.0.243, I receive the error "The key combination (Ctrl+B, Ctrl+E) is bound to command (RedGate.SQLPrompt.SSMSUI.Connect2011.EncapsulateSP) which is not currently available."

    Has this feature been removed from SQL Prompt?
  • Hi John,

    The latest version of the software should fix this bug. Please let us know if you have any issues.

    Best regards,

    David
  • Hi tkrussy,

    We're having problems recreating this problem here. Are any other parts of SQL Prompt working for you (e.g. Format SQL)?

    Best regards,

    David
  • Running 6.5.0.254, SQL 2012, SP2/CU4

    First section is the code as/is.
    Second section shows post-formatting for "Qualify Object Names".
    The when not matched by target insert chooses the wrong qualifier, if one needs to be chosen at all.
            MERGE Employees AS T
            USING StageEmployees AS S
            ON (T.person_id = S.person_id)
            WHEN MATCHED THEN
                UPDATE SET
                        T.last_name = S.last_name,
                        T.first_name = S.first_name,
                        T.middle_name = S.middle_name,
                        T.aka = S.aka,
                        T.empno = S.empno,
                        T.store = S.store,
                        T.hr_is_active = S.hr_is_active,
                        T.job_code = S.job_code
            WHEN NOT MATCHED BY SOURCE THEN
                DELETE
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (last_name,
                        first_name,
                        middle_name,
                        aka,
                        person_id,
                        empno,
                        store,
                        hr_is_active,
                        job_code
                       )
                VALUES (S.last_name,
                        S.first_name,
                        S.middle_name,
                        S.aka,
                        S.person_id,
                        S.empno,
                        S.store,
                        S.hr_is_active,
                        S.job_code
                       );
    
            MERGE Employees AS T
            USING StageEmployees AS S
            ON (T.person_id = S.person_id)
            WHEN MATCHED THEN
                UPDATE SET
                        T.last_name = S.last_name,
                        T.first_name = S.first_name,
                        T.middle_name = S.middle_name,
                        T.aka = S.aka,
                        T.empno = S.empno,
                        T.store = S.store,
                        T.hr_is_active = S.hr_is_active,
                        T.job_code = S.job_code
            WHEN NOT MATCHED BY SOURCE THEN
                DELETE
            WHEN NOT MATCHED BY TARGET THEN
                INSERT (S.last_name,
                        S.first_name,
                        S.middle_name,
                        S.aka,
                        S.person_id,
                        S.empno,
                        S.store,
                        S.hr_is_active,
                        S.job_code
                       )
                VALUES (S.last_name,
                        S.first_name,
                        S.middle_name,
                        S.aka,
                        S.person_id,
                        S.empno,
                        S.store,
                        S.hr_is_active,
                        S.job_code
                       );
    

    Thanks,

    John
Sign In or Register to comment.