SQL Prompt 6.4 beta (Now closed)

• 
  For users of the connection coloring feature: it's now possible to tweak the colors or add new categories by changing

  %localappdata%/Red Gate/SQL Prompt 6/ConnectionColoringCategories.xml
  

  Editing xml files obviously isn't ideal and we are hoping to have a UI to edit these but the first step towards that is getting them loaded from a file which is in this build.

  Aaron L June 04, 2014 06:12

  - 0 +
• 
  In this release, SQL Prompt doesn't scroll the suggestion popup list based on the table alias.
  
  Given the following code, what would you expect the suggestion popup to contain?
  
  select b.<cursor> from APP.TableA a join APP.TableB b on a.TableAID = b.TableAID
  
  I would expect the suggestion list to contain only b.* columns. But it doesn't. Instead, I am shown both a.* and b.* columns. At a minimum, the suggestion list should be scrolled to the first b.* column.
  
  When working with SQL that contains many joins and table alias, this bug means that I need to lift my hands off the keyboard and scroll the list with the mouse. It's not very efficient.
  
  Kevin

  KevinGreiner June 19, 2014 14:41

  - 0 +
• 
  Hi Kevin,
  
  Thanks for finding this for us!
  
  We've fixed this in the latest beta build (6.4.0.204). You can either download it from the download link in this post, or through Check for Updates.
  
  Please let us know if this works for you, and if you find any more problems.
  
  Best regards,
  
  David

  David Priddle June 20, 2014 08:07

  - 0 +
• 
  Thanks, David. The suggestion list is populated as expected with the 204 beta.

  KevinGreiner June 20, 2014 08:28

  - 0 +
• 
  OK, I found another problem. Using the same example,
  
  select b.<cursor> from APP.TableA a join APP.TableB b on a.TableAID = b.TableAID
  
  If a table named exists in the current database, only the columns of that table will be suggested. The columns of the table alias "b" are not shown.

  KevinGreiner June 20, 2014 08:58

  - 0 +
• 
  Hi Kevin,
  I'm looking into recreating this now but I don’t think I'm seeing the same behaviour as you, in the example you've listed I get suggestions from TableB:
  
  
  
  Can you think of anything else that might help us reproduce your issue here?
  
  Thanks,
  Aaron.

  Aaron L June 20, 2014 09:43

  - 0 +
• 
  Looks like the suggestions for the first field are fine. It's the second field that is wrong.
  
  

  KevinGreiner June 20, 2014 10:35

  - 0 +
• 
  Thanks Kevin! I can recreate it fine here with the second field, I'll see what we can do about a fix for this.

  Aaron L June 20, 2014 10:42

  - 0 +
• 
  Hi Kevin,
  
  This should be fixed in the latest beta build (6.4.0.206) - thank you for the awesome repro steps!

  Aaron L June 23, 2014 10:09

  - 0 +
• 
  I notice a lag of 2-3 seconds after using Ctrl-Shift-D to refresh suggestions.
  
  I would like to enable auto-refresh of the suggestion but this lag makes it quite painful when the SSMS UI freezes for 2-3 seconds. This is a development db with several devs making changes.
  
  Here are the counts of objects in the current database.
  
  74 CHECK constraints
  2405 Default or DEFAULT constraints
  432 FOREIGN KEY constraints
  3 Inlined table-functions
  1925 PRIMARY KEY or UNIQUE constraints
  69 Scalar functions
  1399 Stored procedures
  67 System tables
  12 Table functions
  17 Triggers
  2124 User tables
  305 Views

  KevinGreiner July 02, 2014 13:18

  - 0 +
• 
  Hi Kevin,
  
  I'm having difficulty recreating this here. Prompt should start up a thread for refreshing suggestions to avoid locking the UI, but it sounds like it's going wrong somewhere for you.
  I'm think it's not solely related to database size since I've given it a go on one of our giant test databases (~180,000 procs and ~20,000 tables)
  To help narrow it down:
  1. Is this issue new in 6.4 (I'm wondering if it's related to the loading dialog changes we've made) or does it lock up in 6.3 too?
  2. Is it only on that database/server or does it lock up even if connecting to a local instance?
  
  Thank you!
  Aaron.

  Aaron L July 03, 2014 04:09

  - 0 +
• 
  I'm fairly certain this issue is new. I love the new loading dialog and that it's placed at a consistent place on the screen.
  
  The UI freeze is before the dialog is shown. As soon as the small window appears, the UI is responsive again.
  
  I'm unable to reproduce locally even with a large number of objects. Typically, I work remotely, using db servers remotely via VPN connections.

  KevinGreiner July 03, 2014 09:36

  - 0 +
• 
  It does sound like it's related to a slow connection, although I'm still struggling to recreate it here even over a slower connection.
  
  I've had an attempt at reducing the amount of work we do on the UI thread with the progress dialog which might reduce the UI lock up - could you see if 6.4.0.255 helps?

  Aaron L July 03, 2014 10:50

  - 0 +
• 
  Control-B, Control-Q does not work if I try to format an INSERT INTO table() SELECT columns FROM table1 WHERE condition statement.
  I'm also having problems qualifying object names when there are linked server in the query. This one predates SQL Prompt 6.4 beta.

  jmeyer July 11, 2014 10:17

  - 0 +
• 
  Hi jmeyer,
  
  I think I can recreate your issue with the insert into not working correctly if the table being inserted into and the table being selected from have the same column name - I'll look into a fix for you now.
  
  For your linked server issue could you provide an example script of where it's going wrong?
  
  Thanks!
  Aaron.

  Aaron L July 11, 2014 10:27

  - 0 +
• 
  Found another issue: I have set all SQL code to be capitalized. If I type the following statement and after "end" type a space character then "END" will be capitalized. However, if I type a TAB character then it will not be capitalized. If I press the TAB character after "EN" then it will insert the END capitalized from the suggestions.
  
  SELECT CASE WHEN a=1 THEN 0 ELSE 1 end

  jmeyer July 11, 2014 10:49

  - 0 +
• 
  Aaron: Re linked server issue: I unfortunately have not been able to create a simple repo that I can share. The issues are happening in large stored procedures that I'm unable to share

  jmeyer July 11, 2014 10:58

  - 0 +
• 
  Found another formatting issue:
  SELECT *
  FROM dbo.staging_MONOGRAM_INVOICE_fact s
  LEFT JOIN (SELECT agreement_id
  , fm_processing_pct
  , SUM(tot_disbs) AS fm_processing_tot_disbs
  , SUM(tot_disb_amts) AS fm_processing_tot_disb_amts
  , SUM(tot_cancs) AS fm_processing_tot_cancs
  , SUM(tot_canc_amts) AS fm_processing_tot_canc_amts
  , SUM(fm_processing_disb_fee) AS fm_processing_disb_fee
  , SUM(fm_processing_canc_fee) AS fm_processing_canc_fee
  , SUM(fm_processing_tot_fee) AS fm_processing_tot_fee
  FROM dbo.staging_MONOGRAM_MARKETER_CHAN_fact f
  WHERE excl_processing_fee = 0
  GROUP BY agreement_id
  , fm_processing_pct) mc
  ON s.AGREEMENT_ID = mc.AGREEMENT_ID
  
  "ON s.AGREEMENT_ID = mc.AGREEMENT_ID" will be changed to "ON s.AGREEMENT_ID = s.AGREEMENT_ID" and the subquery will not get the "f" aliases inside the sUM() statements

  jmeyer July 11, 2014 11:08

  - 0 +
• 
  Thank you for reporting these! I think I have fixes for your issues in this private build (edit: link removed - changes now in the latest beta), although I wasn't able to recreate the mc alias being changed to s so if it's still happening I might need some more help recreating it.
  
  It's the end of the day here, so I'll have to leave it until Monday before I move these changes into our beta build. Have a good weekend!

  Aaron L July 11, 2014 12:44

  - 0 +
• 
  Release notes state you can execute current batch with ALT-SHIFT-F5. What is the current batch? Is it from the top of the script to the cursor? Is it from the previous GO to the cursor?

  dbbishop July 15, 2014 09:53

  - 0 +
• 
  Hi dbbishop,
  
  Execute Current Batch will look up from the cursor until it finds the previous GO, and then look down from the cursor until it finds the next GO.
  
  If it can't find a GO above, it will use the start of the script. If it can't find a GO below, it will use the end of the script.
  
  Is this the behaviour that you would expect?
  
  Best regards,
  
  David

  David Priddle July 15, 2014 10:01

  - 0 +
• 
  David,
  
  Actually, no. If SHIFT-F5 executes the statement at the cursor, I would expect ALT-SHIFT-F% to execute from previous GO (or start of code) to the cursor, or at least have another option (CTRL-SHIFT-F5) to do that.
  
  Most IDEs (e.g. VS) have an option to "Run to cursor".
  I suppose I could simulate by adding a GO just after the block I want to execute, but heaven forbid if I forget to remove it.

  dbbishop July 15, 2014 10:05

  - 0 +
• 
  Problem: "Format SQL" causes window contents to scroll sometimes to a position that is not useful.
  
  After using, "Format SQL" the window is usually scrolled so the cursor is on the last visible row. Then I always need to scroll the window so I can continue working.
  
  If "Format SQL" doesn't change anything, the window isn't scrolled at all.
  
  Suggestion: maintain the current scroll position. If this isn't possible, scroll the window so the cursor is centered vertically.
  
  (If this is the wrong place for this feedback, please instruct me how to provide this in a better forum.)

  KevinGreiner July 15, 2014 10:14

  - 0 +
• 
  Hi dbbishop,
  
  Would it be possible to give a scenario where this is useful for you?
  
  Would you have this execute from the start of the script to the end of the current statement or execute from the last GO?
  
  Best regards,
  
  David

  David Priddle July 15, 2014 10:24

  - 0 +
• 
  I would expect it to run from the start to the cursor, similar to "run to cursor" capability of Visual Studio. Alternately, it would run from previous 'GO' to cursor. It would be nice to have that as a configuration option, then the user could chose.
  
  As I develop code, I build shells, and then start at the top and develop each shell. I am constantly selecting a block of code and executing it. It would be so convenient to just have the cursor at the end of the block, and press a key to say, run to here.
  
  I may have made a change to code that loads a staging table and subsequent code processes the data in the staging table. Prior to executing the remainder of the code, I want to make sure the expected results are in the staging table, so I highlight code from the start to the end of the load of the staging table, execute it, and look at the results before continuing.
  
  And on it goes...

  dbbishop July 15, 2014 11:16

  - 0 +
• 
  Hi dbbishop,
  
  We'll have this out later this week.
  
  Best regards,
  
  David

  David Priddle July 15, 2014 11:40

  - 0 +
• 
  Hi Kevin,
  
  This is the perfect place for this kind of feedback! I think the scrolling is handled automatically by SSMS after we replace the text at the moment - I'll have a look into seeing if we can override the scroll position.

  Aaron L July 16, 2014 06:46

  - 0 +
• 
  Hi Kevin,
  
  Could you see if 6.4.0.268 works as you'd expect for scrolling after a format? It turned out to be a little more complicated that just keeping the current scroll position since formatting can insert or delete lines which shifts everything around, so I've tried to use the caret position as an anchor point.
  
  Thanks,
  Aaron.

  Aaron L July 16, 2014 12:00

  - 0 +
• 
  Very nice, Aaron. It works 99% perfectly. :-)
  
  When the cursor is in the middle of a line or at the end, it works perfectly. However, when the cursor is at the beginning of a line, the window scrolls one line down and the cursor is moved up one line.
  
  To reproduce:
  1. Put cusor at beginning of a line.
  2. Run "Format SQL".
  3. Note display is scrolled up 1 line and cursor position is changed.
  4. Run "Format SQL" again.
  5. Note that nothings moves on the screen.
  
  One more thing.....
  
  Not new to this beta, but the Options window takes roughly 15 seconds to close now. The first 5-7 seconds are completely frozen without a wait mouse cursor. In prior major releases, this dialog closed quickly.

  KevinGreiner July 16, 2014 13:59

  - 0 +
• 
  Also, "run current statement" (shift-F5) seems broken now.
  
  With only a single statement in a file, it selected all but the last 2 characters. I can send a screenshot if needed.

  KevinGreiner July 16, 2014 14:09

  - 0 +

Add comment

Please sign in to leave a comment.