SQL Prompt 6.4 beta (Now closed)

Aaron L

The stable release announcement can be found here.

Beta closed - thanks to everyone who took part!

The SQL Prompt beta includes:

• Insert semicolons (UserVoice)
• Execute current statement (UserVoice)
• BEGIN...END block highlighting (UserVoice)
• Rename variables (UserVoice) and aliases (UserVoice)
• Improved support for aliases in UPDATE (UserVoice)
• Improved loading dialog box
• Startup performance improvement
• Single suggestion pop-up improvement
• Qualify object names improvements
• Suggestions for additional object types
• Format SQL now matches object case (UserVoice)

Version history
6.4.0.622 - 12th September

• Fix for quotes being stripped after a format SQL (Forum post)
• Fix for incorrect INSERT column highlighting with subqueries in values list

6.4.0.618 - 10th September

• More improvements to expand wildcards indentation
• Support for connecting to Azure restricted accounts
• The error dialog is suppressed for when login fails due to username/password
• Fix for SP-4277 ("textViewMonitor already attached to a TextLinesMonitor")
• A message box is now displayed if the user doesn't have permission to re-use the password from the query's connection in Prompt

6.4.0.609 - 8th September

• Improved handling of dot completion key with suggestions containing dots (Uservoice)
• Improved indentation when expanding wildcards
• Fix for qualify object names with semi-ambiguous columns (Forum post)

6.4.0.602 - 8th September

• Fix for connection details not being auto-populated in VS2010 (Forum post)
• Fix for semicolon being inserted in the wrong position (Forum post)
• Fix for temp table columns not being suggested (Forum post)
• More object types are now suggested from create statements in script

6.4.0.583 - 3rd September

• Limit added for maximum script size that Prompt will cache suggestions for (the default is 5MB)
• Datatypes are suggested if running in a disconnected query
• Fix for suggestions box sometimes displaying a red X

6.4.0.574 - 2nd September

• Suggestions improved for longer scripts (UserVoice, UserVoice and UserVoice)
• Nullability of columns now shown in tooltips, suggestions and object definition box (UserVoice and UserVoice)
• Improved support for table-valued variables and parameters (UserVoice)
• Now suggests stored procedures from script before they've been created in the database (UserVoice)
• Fix for occasional wrong qualification if two tables in different schemas share the same name and column names
• Fix for missing suggestions in CTE if an alias name clashes with CTE name

6.4.0.537 - 26th August

• Highlighting columns and data in INSERT statements (UserVoice)
• Improvements to snippet suggestions after aliases (UserVoice)
• Partial matching enabled for snippets (UserVoice)
• Added Remove Square Brackets feature to remove unnecessary square brackets from a script
• Fix for find invalid objects with table valued function (Forum post)
• Fix for expanding wildcards with inserted/deleted tables in OUTPUT statement (Forum post)
• Fix for tab not always expanding wildcards (Forum post)
• Fix for object tooltips on hover not displaying in Visual Studio

6.4.0.506 - 18th August

• Added option for "Indent BEGIN...END blocks" (Forum post and UserVoice)
• Grouped similar commands together in SQL Prompt menu
• Both keywords are now highlighted for BEGIN TRY...END TRY matching
• Query hint option IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX is now suggested

6.4.0.500 - 15th August

• Fix for snippet being expanded with tab while suggestions list is displayed (Forum post)
• Fix for qualify object names with table valued variables (Forum post)
• Suggestions added for FEDERATION related keywords on Azure
• Synonym population won't request base types unless "Load referenced databases" is on (which could have been causing slow loading or timeouts)

6.4.0.478 - 13th August

• Tweaked the text highlighting for modified text

6.4.0.476 - 13th August

• Identifiers will now be recased to match the case used in the database with Apply Casing Options and Format SQL (UserVoice)
• Text modified due to casing options is now highlighted
• Added option to limit number of tabs that will be reopened on startup (the default is 20) (UserVoice)
• No longer try to avoid alias name clashes with stored procedure names (UserVoice)
• Fix for Insert Semicolons causing a duplicate semicolon in DECLARE CURSOR and SET CURSOR statements
• Fix for occasional duplicate columns in wildcard expansion

6.4.0.457 - 8th August

• Fix for duplicate columns sometimes being suggested
• Fix for selection changing after a format SQL

6.4.0.449 - 7th August

• Methods now suggested for geometry and geography clr types (UserVoice)
• Native intellisense is now re-enabled when Prompt suggestions are disabled (UserVoice)
• Tables and views suggested if CREATE statement in script
• ON <owner_name> now auto completed for DISABLE/ENABLE TRIGGER and ALTER/DROP INDEX
• Removed invalid keyword suggestions after a WAITFOR
• Faster SSMS command initialization during startup
• Fix for parameter suggestions missing after an EXEC statement (Forum post)

6.4.0.404 - 1st August

• Fix for bug introduced in 6.4.0.402's installer where Prompt might not install correctly to SSMS or Visual Studio.
  If you're missing Prompt from the previous build then installing the latest build should bring it back. Sorry for the inconvenience!

6.4.0.402 - 1st August

• Installer options to install to SSMS and/or Visual Studio (UserVoice)
• Fix for Qualify Object Names adding in an alias qualification to an UPDATE statement incorrectly
• Static method suggestions added to hierarchyid

6.4.0.397 - 31st July

• Triggers in ALTER TABLE suggestions are now filtered by the table (UserVoice)
• Columns in CREATE TABLE statement are suggested for PRIMARY and FOREIGN keys
• CLR methods suggested for hierarchyid
• Fix for contained database users with windows authentication
• Fix for formatting error with "GO 10"
• Potential fix for double dot syntax (Forum post)
• Minor performance (~20%) and memory (~10%) improvements for database suggestions
• Suggestions for additional object types
  • Sequences (UserVoice)
  • Check constraints
  • Queues (Uservoice)
  • Services
  • Contracts
  • Message types
  • Routes
  • Xml schema collections

6.4.0.300 - 18th July

• Comments are now preserved when completing ALTERs (UserVoice and Forum post)
• Support for connecting as a user on contained database (Used to display "Login failed for user...")
• Execute to cursor added as Ctrl+Shift+F5 (Forum post)

6.4.0.292 - 17th July

• Fix for execute current statement not selecting the correct text (Forum post)
• Execute current statement now works when connected to an Azure server
• $USER$ snippet placeholder will now resolve to the connected user rather than the windows user
• Caret stays in the same location after a Format SQL if positioned at start of line (Forum post)

6.4.0.286 - 16th July

• Scroll position is now maintained after applying Format SQL (Forum post)
• Fix for ON being misaligned if table hints are used in a join clause (UserVoice)

6.4.0.284 - 16th July

• No longer freezes when disabling/enabling suggestions during a database load

6.4.0.282 - 15th July

• Generated aliases are now enclosed in square brackets if option set (UserVoice)
• If enclose square brackets is off Prompt will avoid generating aliases that match keywords
• Qualify object names and apply casing options are now only applied to the selected text (rather than entire line)

6.4.0.279 - 14th July

• Added an option to limit the maximum script size that tab history will save (defaults to 1MB)
• Fixes for qualify object names with columns (Forum post)

6.4.0.266 - 10th July

• Added Alt+Shift+F5 to execute current batch (this is in addition to Shift+F5 to execute current statement)
• Columns and datatypes in object definition box are now sortable (Forum post)
• Fix for too many suggestions (Forum post)

6.4.0.256 - 7th July

• Snippet manager now keeps edited and created snippets in view after refreshing list
• Database loading dialog now has a tooltip on hover to show database names when multiple databases are loading

6.4.0.255 - 3rd July

• Reduced locking on UI thread during database loading (hopefully fixing the forum post below)

6.4.0.249 - 3rd July

• Generated comments are placed on the same line as data in INSERT statements if "Place commas at start" has been set (UserVoice and UserVoice)

6.4.0.245 - 2nd July

• Minor fix for default schema syntax on system databases having too many suggestions
• Character limit removed from create snippet text box
• Qualify object names improvements:
  • New option for qualifying with aliases (UserVoice)
  • Changes are now highlighted
  • No longer displays errors if temporary tables are created in the script
  • Now uses the existing suggestion cache rather than recaching some objects
  • Will replace table qualification with its alias if table has been aliased

6.4.0.206 - 23rd June

• INSERT and SELECT formatting tweak is now optional (Forum post)
• Column suggestions are now displayed for a table alias rather than a table of the same name (Forum post)

6.4.0.204 - 20th June

• Parameter info tooltip is now only displayed after typing (or with the shortcut ctrl+shift+space) and can be hidden with escape
• Fix for columns suggestions not filtered by alias (Forum post)
• Table keyword now cased as a datatype in declare statements and table valued functions (Forum post)

6.4.0.184 - 9th June

• Fix for some assemblies not being digitally signed correctly (causing false positives with anti-virus software)

6.4.0.177 - 9th June

• Rename alias improvements
• Now hides green highlights after inserting semicolons if suggestions are disabled
• Support for suggestions using default schema syntax (eg "master..sp_help")
• System objects shown if fully qualified to system database
• SSMS built in intellisense disabled on startup if prompt is enabled (Forum post)
• INSERT and SELECT formatting tweak (Forum post)

6.4.0.167 - 4th June

• No longer throws exception with connection coloring in certain circumstances

6.4.0.165 - 4th June

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

Aaron L

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.

KevinGreiner

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

David Priddle

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

KevinGreiner

Thanks, David. The suggestion list is populated as expected with the 204 beta.

KevinGreiner

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.

Aaron L

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.

KevinGreiner

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

Aaron L

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

Hi Kevin,

This should be fixed in the latest beta build (6.4.0.206) - thank you for the awesome repro steps!

KevinGreiner

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

Aaron L

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.

KevinGreiner

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.

Aaron L

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?

jmeyer

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.

Aaron L

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.

jmeyer

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

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

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

Aaron L

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!

dbbishop

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?

David Priddle

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

dbbishop

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.

KevinGreiner

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.)

David Priddle

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

dbbishop

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...

David Priddle

Hi dbbishop,

We'll have this out later this week.

Best regards,

David

Aaron L

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

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.

KevinGreiner

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

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.