SQL Prompt 6.3 beta (Now closed)

Aaron L

Beta closed - thanks to everyone who took part!


This latest beta build of SQL Prompt includes:

New features

• Select in Object Explorer (context menu as well as Ctrl + F12) (UserVoice)
• Many improvements to our SQL grammar
• UpperCamelCase & lowerCamelCase casing options
• Highlighting matching parentheses (UserVoice)
• Template parameter dialog box now opens automatically if a snippet has parameters (Forum post)
• $SELECTIONSTART$ and $SELECTIONEND$ snippet placeholders (Documentation)
• Improvements to when Prompt suggestions box is displayed (suggestions are now displayed if you continue to type a half completed word)
• Pressing Enter after GO now enters a new line
• Inserted and Deleted tables now suggested for triggers (and now resolve to correct table in other statements) (UserVoice)
• String constant suggestions for SERVERPROPERTY, FILEPROPERTY, DATABASEPROPERTYEX, etc
• Suggestions box now auto-hides when there are no suggestions
• Partial matching system improvements (UserVoice)
• More suggestions for new 2014 syntax
• Show schema names button state is now saved (UserVoice and UserVoice)

New in 6.3.0.294

• Suggestions box delay option added (UserVoice)
• More documentation for built-in functions
• More string literal suggestions

Feedback
If you encounter any issues with the beta, post them on this forum.

If you have any suggestions for improvements to SQL Prompt, post them on our suggestions forum.


Bug fix version history

6.3.0.252

• Columns suggested first in search conditions (eg WHERE and HAVING) (Forum post)
• Auto-completed functions won't insert closing parenthesis if directly before a column (eg. "SUM(Column" instead of "SUM()Column")
• Column names on expand wildcard are no longer qualified with schema names
• Fixes for several bugs that were sent to us as error reports from SQL Prompt

6.3.0.240

• Improved suggestions after errors in script (Forum post)
• Improved suggestion ordering (Forum post)
• Minor performance improvements

6.3.0.188

• Select in object explorer now selects objects on a connected linked server (Forum post)
• Select in object explorer now works with table types and table-valued functions (Forum post)
• Fix for "value" stopping suggestions (Forum post)
• Fix for suggestions stopping (Forum post)
• Fix for SP-4994 (Unable to load DLL 'SQLite.Interop.dll')
• Fix for SP-3598 & SP-4984 (Very large encrypted stored procedures are not decrypted properly)

6.3.0.121

• Fix for "GO 10" crash (Forum post)

6.3.0.102

• Auto generated insert statements now use GETDATE() instead of a string literal (UserVoice)

6.3.0.100

• Fix for CamelCase datatypes enclosed in square brackets
• Fix for SP-5013 (NullReferenceException)

6.3.0.95

• Fix for xml casing (Forum post)
• Fix for select in object explorer failing in SSMS2008R2 (SP-5006/"Field 'Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.hierarchies' not found.")
• A few missing suggestions added for built in functions

6.3.0.70

• Auto generated insert statements with hints and a semicolon will keep the hints/semicolon (Forum post)

6.3.0.66

• Fix for missing options (eg custom aliases)

6.3.0.65

• Fix for snippet $SELECTIONSTART$ selecting wrong text across multiple lines with indentation
• Select in object explorer works if the cursor is on a semi-colon (Forum post)
• Insert statements keep the table hints with the name when generating insert code (Forum post)
• More grammar fixes

6.3.0.39

• Fix for keywords occasionally not being recased (Forum post)
• Fix for wrong suggestion being selected
• Fix for ‘left’ not being cased correctly (Forum post)
• Master database is enclosed with square brackets if the option is set
• Various keyword casing fixes
• Fixed the following bugs that were sent to us as error reports from SQL Prompt: SP-3556, SP-4388, SP-3680, SP-3860, SP-4258, SP-4379, SP-4192, SP-3545, SP-4948, SP-4982

PDinCA

Option to turn OFF bracket-matching, please?

I find that SSMS Intellisense limited to code blocks and bracket-matching works extremely well with SQL Prompt. I wouldn't like to see the two products argue over who will highlight a bracket, or not.

Thanks.

Aaron L

Hi Stephen,

I don't think they argue over who gets to highlight a parenthesis, if you do notice any issues please let us know and we'll fix them.

JBSandhills

When formatting SQL XML statement like the following with the XML method "nodes" on my computer, nodes becomes capitalized, and this capitalization causes the query to fail. Also, I noticed that formatting puts a space between the "T" and "(x)". I don't remember that happening in previous versions.

It should be:

SELECT  T.x.value('./@Value1', 'INT'), T.x.value('./@Value2', 'INT')
FROM    @XMLVariable.nodes('RawData/RawDataRecord') AS T(x)

After formatting:

SELECT  T.x.value('./@Value1', 'INT'), T.x.value('./@Value2', 'INT')
FROM    @XMLVariable.NODES('RawData/RawDataRecord') AS T (x)

I never had problems with the node keyword or its alias before the beta, so I think it might be a bug. Can you please have someone look into it?

Thanks.

Aaron L

Hi JBSandhills,
Thanks for reporting this - I can recreate the "nodes" being uppercased and will look into a fix for you.

For the space between the T and the (x) I think that it might have always been like that (I just gave it a try 6.2 and 5.3). Is this something you'd want changed?

JBSandhills

Hi Aaron,
It would be nice to have the space removed because it looks a bit strange; however, it's not the end of the world if this feature has to come sometime later. As long as the NODES issue is fixed by stable release, my coworkers and I will be very happy.

Aaron L

The xml casing should be fixed for you in 6.3.0.95.

Mr.DiGi

Microsoft SQL Server Management Studio 11.0.3401.0
SQL Prompt Pro 6.3.0.102
SQL Search 1.1.9.57
SSMS Tools Pack 2.7.2.0

Instant crash after typing number after GO. In SSMS you can specify how many times should SSMS repeat statement.

PRINT 'You will see this message 10 times'
GO 10

This works fine with SQL Prompt 6.2.

Edit: Yay, just paste example in SSMS and...

Aaron L

Hi Mr.DiGi,

I'm really sorry you've encountered this crash but thank you for reporting it. I can recreate it here and will look into a fix for you, hopefully I'll have a new build out shortly.

Thanks,
Aaron.

Aaron L

I've just uploaded 6.3.0.121 which should have a fix for this crash. Thanks again for reporting it!

Mr.DiGi

No problem, thanks for quick fix.

Mr.DiGi

More (minor) bugs - User defined table type related:

- Select in Object Explorer is not working for table type
- column names from table type are not listed (maybe this didn't work with previous version too)
- we have custom table type with "Value INT" column. After using column "Value" in join all database object/names disappears from SQL Prompt (only snippets are listed).

demo:

CREATE TYPE [dbo].[IntTable] AS TABLE
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Value] [int] NULL
)
GO

DECLARE
	@x dbo.IntTable

SELECT
	1
FROM
	@x AS X
	INNER JOIN sys.objects AS O ON O.object_id = X.Value
	INNER JOIN <no hint here>

Just changing "O.object_id = X.Value" to (invalid) "O.object_id = X.ValueZZZ" brings SQL Prompt back.

Edit: Or we can use O.object_id = X.[Value] - maybe because XML type .value, .nodes hacks?

Aaron L

Thanks for reporting these, I can recreate them here and we're looking into fixes for them.

I think you are right about the value being mixed up with the xml type value and so Prompt is wrongly expecting parentheses and parameters, which results in it not giving you the correct suggestions.

Aaron L

Hi Mr DiGi,
Just to let you know, the bugs you mentioned in your last post should be fixed in 6.3.0.188.

Thanks,
Aaron.

Mr.DiGi

Awesome, thanks for fixes.

OT: Is there any way to log/debug Qualify object names call? It just hang for our db (almost since beginning).

No error in "....\AppData\Local\Red Gate\SQL Prompt 6\sqlpromptlog-yyyymmdd-hhmmss.log"

PDinCA

Attributes used to come first for WHERE clauses - now they are last and that's highly counterproductive. I get other table then function names before columns. Is it just me? Using the 188 build, win8.1 x64, SQL2012EE

Aaron L

Hi PDinCA,
I'm having difficulty recreating your issue - I always get columns suggested first on a where clause. Would it be possible to get a script or a screenshot of where this is happening for you?

Mr.DiGi, Unfortunately I don't think there's enough logging in the current build to help figure out where qualify object names is going wrong. Does it hang every time for you? Even on older, non-beta builds?

PDinCA

Yesterday's script:

INSERT INTO dbo.t_company_custom_def
     ( custom_id
     , custom_company_id
     , custom_xml
     , update_date
     , update_by
     )
SELECT @NewID
     , 30
     ,[custom_xml]
     , '20140305 15:45'
     , 'me'
  FROM [xcp_ref].[dbo].[t_company_custom_def] ccd
 WHERE co

First off, having completed the WHERE and a space, it gave me ccd, the alias, as expected, and correct. I typed c and the ccd alias reamined at the top, followed by columns, so I typed an o, to get to "WHERE co" as in the code above. That's when the whole list changed to a list of f(x) functions, headed by COALESCE, then CONTAINS, then a bunch of tables (I have them listed by schema), then a slew of sys.functions, and I don't see the "custom_COmpany_id" (in the enlarged pop-up) that I expected my CO typing to bring to the top of the list. SQL Prompt at this point becomes useless for rapid code completion.

I would really like to send you a SnagIt or Snip, but Windows8.1 has completely destroyed my ability to use them. I hope you can create a simple table like the above, and reproduce the problem...

Failing that, a remote diagnostic session would be fine by me.

Aaron L

Hi PDinCA,
I just gave your script a try and I think this behavior might be by design. Prompt will display any objects matched by the prefix first (COALESCE, CONTAINS etc all start with CO) followed by any partial matches.

I get the column listed much further down as it's not considered that "good" a match yet:


To get a better match against the columns you should be able to type the first letter or two of each word (eg. Typing "cu_co", "cco" or even "_co" will give a better match against custom_company_id)

Edit: as of 6.3.0.240 the columns should be at the top of the partial matches list, objects matched by a prefix are still first however.

PDinCA

I must admit to being surprised by the volume of what I will call "noise" listed above potential columns that are partially matched.

Thinking about the context of the SQL Prompt suggestions, when one is writing a SQL WHERE clause, how frequently is a column in a FROM clause the actual subject of the WHERE versus some obscure system function, another table name (huh?), or a function? I'd suggest that in excess of 95% of the time the COLUMN is what is wanted... It is acknowledged BAD PRACTICE to use functions in WHERE clauses anyway, or so I believe.

I'm out of votes on SQL Prompt (two logins' worth), so may I here suggest:

Provide an option to "always list columns in suggestion lists, even partial matches, above any functions of any kind".

In over 25 years of coding SQL, I can safely say the number of times I want a built-in or sys. function as the FIRST artifact after my WHERE is 0.05% of the time, or less!

Doesn't "columns first" simply just make sense? Or maybe I really am missing something...

Aaron L

Hi PDinCA,
Could you give 6.3.0.252 a try and see if that's closer to what you'd prefer? (We normally sort by prefix/partial match and then the object type, build 252 should reverse this so columns and aliases are kept at the top for the WHERE clause)

PDinCA

Thanks Aaron, installed and makes a lot more sense. Tried several partial matches on wide tables and everything looks good. Appreciate the swift turnaround.