SQL Prompt 7.1 Beta (now closed)

Aaron L

The stable release announcement can be found here.

Beta closed - thanks to everyone who took part!
This build of SQL Prompt includes:

• Highlight matching objects experimental feature (UserVoice)
• Keyword are now suggested for new 2016 syntax:
  • Row-level security (MSDN)
  • Dynamic data masking (MSDN)
  • Always encrypted (MSDN)
  • Stretch tables (MSDN)
  • System versioning (MSDN)
  • Query store options (MSDN)
• Results grid context menu items in SSMS and Visual Studio 2012 and above:
  • Script selection as INSERT statement (UserVoice)
  • Copy selection as an IN clause (UserVoice)
  • Script selection to Excel (UserVoice)
• Option to disable tab history (UserVoice)
• Tab history now includes a Closed tab (UserVoice)
• Support for using columns in self-joins with Qualify Object Names (Forum post)
• You can now right-click to copy error messages from the database loading dialog box

Version history
7.1.0.244 - 11th January

• Logins are now suggested
• Transactions are now suggested
• Transactions can now be renamed with F2
• Fix for a scalar datatype being treated as table-valued if it has the same name as a table (Forum post)
• Fix for support ticket #57678: index being incorrectly qualified with a table name

7.1.0.234 - 7th January

• Variables are now suggested and highlighted in more statements
• Ambiguous column message is no longer displayed when running Qualify Object Names (Forum post)
• Fix for SP-5664: "hexadecimal value 0x00, is an invalid character" being displayed when exporting to Excel

7.1.0.210 - 16th December

• Columns from PIVOT/UNPIVOT clauses are now suggested (UserVoice and UserVoice)
• Additional statements are now supported when using Highlight match objects experimental feature

7.1.0.188 - 9th December

• Highlight matching objects experimental feature (UserVoice)
• Constraints are now suggested for ALTER TABLE/DROP CONSTRAINT
• Improved performance of column dependencies box
• Reduced memory usage when previewing object creation scripts
• IDENTITY columns are now recognised in table variables (Forum post)

7.1.0.144 - 25th November

• New JSON functions in 2016 CTP3 are now suggested
• New DROP IF EXISTS syntax in 2016 CTP3 is now suggested
• Excel files exported from results grid now have a datetime format (Forum post)
• Fix for wildcards being expanded using the wrong table (Forum post)

7.1.0.118 - 11th November

• Added results grid context menu items into Visual Studio 2012 and above
• Added script selection to excel results grid context menu item (UserVoice)
• Fixed bug when using the All non-aggregated columns suggestion in certain circumstances (Forum post)

7.1.0.112 - 20th October

• Tab history now includes a Closed tab (UserVoice)
• Support for using columns in self-joins with Qualify Object Names (Forum post)
• You can now right-click to copy error messages from the database loading dialog box

7.1.0.88 - 5th October

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

kondrich

Hello,

it seems that download file was not updated to 7.1.0.112.

Best regards,

Klaus

David Priddle

Hi Klaus,

Thank you for finding this!

I've updated the zip file with the new release.

Best regards,

David

kondrich

Hi David,

thank you for updating the file.

Best regards,

Klaus

sdks

Hi,
bad formatting (FOR XML PATH):

SELECT  @MissingProducts = STUFF(
										  (SELECT   N', '
                                                    + CAST(Id AS VARCHAR(30))
                                           FROM     @ProductsWithPrices p
                                           WHERE    NOT EXISTS ( SELECT
                                                              *
                                                              FROM
                                                              dbo.Product pp
                                                              WHERE
                                                              pp.Article = p.Id )
                FOR                      XML PATH('') ,
                                             TYPE).value('text()[1]',
                                                         'nvarchar(max)'), 1,
                                         2, N'');

David Priddle

Hi sdks,

If you could put this and any other formatting suggestions you have in our formatting survey, that would be great.

We're working on upgrading the formatter so that it is more powerful and flexible, so having your suggestions would help a lot.

Best regards,

David

sdks

Done. Another bug with GROUP BY suggestions. When auto-completing non aggregate columns it did not include "s.dir" :

SELECT  ProductId ,
                        COUNT(*) * s.dir AS Qty ,
                        NULL AS LocationId
                FROM    dbo.SalesOrder_Product
                        CROSS APPLY ( SELECT    [dbo].[fn_SalesOrderProductQtyChangeSignUnit](@PreviousStatusCode,
                                                              @OrderStatusCode) AS dir
                                    ) s
                WHERE   SalesOrderId = @SalesOrderId
                        AND s.dir IS NOT NULL
				GROUP BY ProductId

(120,1): SQL72014: .Net SqlClient Data Provider: Msg 8120, Level 16, State 1, Procedure p_UpdateSalesOrderStatus, Line 28 Column 's.dir' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

David Priddle wrote:

Hi sdks,

If you could put this and any other formatting suggestions you have in our formatting survey, that would be great.

We're working on upgrading the formatter so that it is more powerful and flexible, so having your suggestions would help a lot.

Best regards,

David

David Priddle

Hi sdks,

Thanks for identifying this! I can reproduce it and am working on a fix now.

Best regards,

David

sdks

Another bug. SHIFT+F5 does not work then there are syntax errors in the window. For example:

SELECT 1
SELECT * FROM

If I just highlight "SELECT 1" I can execute it. However when the cursor is somewhere between "SELECT 1" statement and hit SHIFT+F5 it gives following error:

---------------------------
SQL Prompt - Execute current statement
---------------------------
SQL Prompt was unable to complete this operation.

Problem areas have been highlighted.
---------------------------
OK   
---------------------------

Aaron L

Hi sdks,

This is what we'd expect to see. If there's an error in the current batch then "Execute current statement" will display an error rather than potentially execute too much or too little (which would be pretty risky).

If you place a "GO" batch separator between the error and the statement you're working on it should be able to accurately execute the current statement for you.

Thanks,
Aaron.

stanac

There is a bug in highlighting selected variable. Take the following query for example

DECLARE @theXml XML = '<a>b</a>'
SELECT t.node.value('.', 'VARCHAR') FROM @theXml.nodes('/') AS t(node)

After clicking on the variable name in the declare line, same variable in not highlighted on the second line.

Aaron L

Thanks stanac - the highlight matching objects experimental feature is still under development and we've still got a few more objects/statements that we need to "hook up" to get them highlighting correctly. We'll look into getting this one fixed.