SQL Prompt 6.4 beta (Now closed)
Aaron L
Posts: 596 New member
The stable release announcement can be found here.
Beta closed - thanks to everyone who took part!
The SQL Prompt beta includes:
Version history
6.4.0.622 - 12th September
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.
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
- 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
- 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)
- 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
- 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
- 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
- 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
- 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
- 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)
- Tweaked the text highlighting for modified text
- 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
- Fix for duplicate columns sometimes being suggested
- Fix for selection changing after a format SQL
- 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)
- 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!
- 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
- 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
- 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)
- 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)
- 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)
- No longer freezes when disabling/enabling suggestions during a database load
- 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)
- 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)
- 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)
- 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
- Reduced locking on UI thread during database loading (hopefully fixing the forum post below)
- Generated comments are placed on the same line as data in INSERT statements if "Place commas at start" has been set (UserVoice and UserVoice)
- 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
- 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)
- 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)
- Fix for some assemblies not being digitally signed correctly (causing false positives with anti-virus software)
- 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)
- No longer throws exception with connection coloring in certain circumstances
- 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.
Comments
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
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
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.
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.
This should be fixed in the latest beta build (6.4.0.206) - thank you for the awesome repro steps!
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
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.
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.
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?
I'm also having problems qualifying object names when there are linked server in the query. This one predates SQL Prompt 6.4 beta.
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.
SELECT CASE WHEN a=1 THEN 0 ELSE 1 end
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
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!
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
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.
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.)
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
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...
We'll have this out later this week.
Best regards,
David
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.
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.
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.
With only a single statement in a file, it selected all but the last 2 characters. I can send a screenshot if needed.