SQL Prompt 6.5 beta (Now closed)

Aaron L · February 10, 2015 8:48AM

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.

KevinGreiner · February 12, 2015 8:29AM

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 L · February 12, 2015 8:57AM

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.

KevinGreiner · February 12, 2015 9:20AM

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 L · February 12, 2015 9:40AM

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.

KevinGreiner · February 13, 2015 11:59AM

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;

bstrautin · February 13, 2015 12:21PM

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 L · February 16, 2015 4:15AM

Hi Kevin and bstrautin,

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

Thanks!
Aaron.

sdks · February 17, 2015 6:15AM

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.

mjharper · February 17, 2015 9:03AM

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

David Priddle · February 17, 2015 9:07AM

Hi Matt,

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

Best regards,

David

Aaron L · February 17, 2015 9:09AM

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.

David Priddle · February 17, 2015 11:48AM

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

mjharper · February 18, 2015 5:18AM

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

sdks · February 18, 2015 6:34AM

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;

David Priddle · February 18, 2015 9:39AM

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 L · February 18, 2015 9:47AM

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.

sdks · February 19, 2015 6:10AM

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.

mjharper · February 19, 2015 11:27AM

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.

David Priddle · February 19, 2015 11:51AM

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

David Priddle · February 20, 2015 9:51AM

Hi Matt,

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

Best regards,

David

mjharper · February 23, 2015 12:00PM

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

mjharper · February 23, 2015 12:15PM

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

David Priddle · February 24, 2015 10:26AM

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

jsreynolds1 · February 24, 2015 7:58PM

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(*) &gt; 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(*) &gt; 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

David Priddle · February 25, 2015 9:09AM

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

jsreynolds1 · February 25, 2015 12:03PM

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;

tkrussy · February 25, 2015 1:33PM

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?

David Priddle · February 27, 2015 8:50AM

Hi John,

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

Best regards,

David

David Priddle · February 27, 2015 8:54AM

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

jsreynolds1 · February 27, 2015 6:12PM

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

SQL Prompt 6.5 beta (Now closed)

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk