SQL Prompt 6.5 beta (Now closed)

• 
  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?

  KevinGreiner February 12, 2015 08:29

  - 0 +
• 
  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.

  Aaron L February 12, 2015 08:57

  - 0 +
• 
  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.

  KevinGreiner February 12, 2015 09:20

  - 0 +
• 
  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.

  Aaron L February 12, 2015 09:40

  - 0 +
• 
  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;
  

  KevinGreiner February 13, 2015 11:59

  - 0 +
• 
  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 
  

  bstrautin February 13, 2015 12:21

  - 0 +
• 
  Hi Kevin and bstrautin,
  
  Thank you for the bug reports! we can recreate both locally and we're looking into fixes now.
  
  Thanks!
  Aaron.

  Aaron L February 16, 2015 04:15

  - 0 +
• 
  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.

  sdks February 17, 2015 06:15

  - 0 +
• 
  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

  mjharper February 17, 2015 09:03

  - 0 +
• 
  Hi Matt,
  
  Thanks for finding this! We can reproduce this and we're looking into a fix for it now.
  
  Best regards,
  
  David

  David Priddle February 17, 2015 09:07

  - 0 +
• 
  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.

  Aaron L February 17, 2015 09:09

  - 0 +
• 
  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

  David Priddle February 17, 2015 11:48

  - 0 +
• 
  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

  mjharper February 18, 2015 05:18

  - 0 +
• 
  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;
  

  sdks February 18, 2015 06:34

  - 0 +
• 
  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

  David Priddle February 18, 2015 09:39

  - 0 +
• 
  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.

  Aaron L February 18, 2015 09:47

  - 0 +
• 
  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.

  sdks February 19, 2015 06:10

  - 0 +
• 
  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.

  mjharper February 19, 2015 11:27

  - 0 +
• 
  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 19, 2015 11:51

  - 0 +
• 
  Hi Matt,
  
  We've included the fix in the latest download. Please let us know if you have any issues.
  
  Best regards,
  
  David

  David Priddle February 20, 2015 09:51

  - 0 +
• 
  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:00

  - 0 +
• 
  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

  mjharper February 23, 2015 12:15

  - 0 +
• 
  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

  David Priddle February 24, 2015 10:26

  - 0 +
• 
  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

  jsreynolds1 February 24, 2015 19:58

  - 0 +
• 
  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

  David Priddle February 25, 2015 09:09

  - 0 +
• 
  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;
  

  jsreynolds1 February 25, 2015 12:03

  - 0 +
• 
  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?

  tkrussy February 25, 2015 13:33

  - 0 +
• 
  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 08:50

  - 0 +
• 
  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

  David Priddle February 27, 2015 08:54

  - 0 +
• 
  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

  jsreynolds1 February 27, 2015 18:12

  - 0 +

Add comment

Please sign in to leave a comment.