How can we help you today? How can we help you today?

The latest stable build of SQL Prompt is 9.1.4.4532 (21st March)

Download SQL Prompt

What's new?

This build of SQL Prompt includes these improvements:


  • Added refactoring option to prefix 'N' to literal strings when assigning to a unicode NCHAR/NVARCHAR variable. Found under Prompt -> Options -> Style.
  • Added 6 new code analysis rules, bringing the total to 94: 
             BP013, BP022, BP0023, BP024, DEP022, PE023.
  • SP-6675, SP-6851 : Fixed out of memory exception that could trigger when processing table hints.
  • SP-6707 : Rule ST002 no longer detects issue on column_alias=expression syntax.
  • SP-6847 : Added support for WITHIN GROUP order clause on STRING_AGG function.
  • SP-6853 : Fix CTE/Cursor formatting error when automatically insert semicolons option is on.
  • SP-6865 : 'Align data types and constraints' schema (DDL) formatting option could cause an argument null exception.
See the full release notes for full details

Feedback

If you find any issues 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.
Michael Clark
0

Comments

6 comments

Sort by
  • jgonnering
    getting errors with this version formatting SQL (CTRL+K, CTRL+Y). I do not get error using default formatting style. Email me for SQL being formatted and format file.
    from log file with verbose logging:
    09 Mar 2018 21:07:21,011 [35] WARN RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2 - Error at range (0:1): System.ArgumentNullException: Value cannot be null.
    Parameter name: token
    at Format.Engine.Formatting.RuleCollection.Rules.VerticallyLinkedTokensRuleExtensions.AlignmentPoint(VerticallyLinkedTokensRule verticallyLinkedTokensRule, TSqlParserToken token)
    at Format.Engine.Formatting.RuleProviders.Common.ProcedureParameterRuleProvider.AlignTo(IList`1 procedureParameters, TSqlFragment parentFragment)
    at Format.Engine.Formatting.RuleProviders.CreateAlterProcedureRuleProvider.FormatProcedureStatementBody(ProcedureStatementBody fragment)
    at Format.Engine.Formatting.RuleProviders.Visitors.TopLevelVisitor.Visit(AlterProcedureStatement fragment)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(AlterProcedureStatement node)
    at Microsoft.SqlServer.TransactSql.ScriptDom.AlterProcedureStatement.Accept(TSqlFragmentVisitor visitor)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlBatch.AcceptChildren(TSqlFragmentVisitor visitor)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(TSqlBatch node)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlBatch.Accept(TSqlFragmentVisitor visitor)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlScript.AcceptChildren(TSqlFragmentVisitor visitor)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(TSqlScript node)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlScript.Accept(TSqlFragmentVisitor visitor)
    at Format.Engine.Formatting.SqlFormatter.Format(String script, IScriptWriter scriptWriter, CancellationToken cancellationToken, IList`1& parseErrors)
    at RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2.FormatScript(String script, LayoutOptions activeStyle, Int32 tabSize, IList`1& formatErrors, CancellationToken cancellationToken)
    at RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2.FormatScript(String script, String activeStyleName, Int32 tabSize, ITextReplacer textWriter, Int32 startIndex)
    09 Mar 2018 21:07:23,048 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    09 Mar 2018 21:07:23,049 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    09 Mar 2018 21:07:23,050 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    09 Mar 2018 21:07:23,051 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    09 Mar 2018 21:07:23,052 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 118660780
    09 Mar 2018 21:07:23,053 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    09 Mar 2018 21:07:23,054 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    09 Mar 2018 21:07:25,443 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    09 Mar 2018 21:07:25,447 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    09 Mar 2018 21:07:25,450 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 118660780
    jgonnering
    0
  • jgonnering
    still getting errors using the March 14th release trying to format using a custom style.

    15 Mar 2018 16:36:19,679 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:19,680 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:19,682 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:19,683 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:19,683 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 140401580
    15 Mar 2018 16:36:19,684 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:19,685 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:25,677 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:25,677 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:25,678 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 140401580
    15 Mar 2018 16:36:25,708 [107] WARN  RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2 - Error at range (0:1): System.ArgumentNullException: Value cannot be null.
    Parameter name: token
       at Format.Engine.Formatting.RuleCollection.Rules.VerticallyLinkedTokensRuleExtensions.AlignmentPoint(VerticallyLinkedTokensRule verticallyLinkedTokensRule, TSqlParserToken token)
       at Format.Engine.Formatting.RuleProviders.Common.ProcedureParameterRuleProvider.AlignTo(IList`1 procedureParameters, TSqlFragment parentFragment)
       at Format.Engine.Formatting.RuleProviders.CreateAlterProcedureRuleProvider.FormatProcedureStatementBody(ProcedureStatementBody fragment)
       at Format.Engine.Formatting.RuleProviders.Visitors.TopLevelVisitor.Visit(AlterProcedureStatement fragment)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(AlterProcedureStatement node)
       at Microsoft.SqlServer.TransactSql.ScriptDom.AlterProcedureStatement.Accept(TSqlFragmentVisitor visitor)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlBatch.AcceptChildren(TSqlFragmentVisitor visitor)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(TSqlBatch node)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlBatch.Accept(TSqlFragmentVisitor visitor)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlScript.AcceptChildren(TSqlFragmentVisitor visitor)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(TSqlScript node)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlScript.Accept(TSqlFragmentVisitor visitor)
       at Format.Engine.Formatting.SqlFormatter.Format(String script, IScriptWriter scriptWriter, CancellationToken cancellationToken, IList`1& parseErrors)
       at RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2.FormatScript(String script, LayoutOptions activeStyle, Int32 tabSize, IList`1& formatErrors, CancellationToken cancellationToken)
       at RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2.FormatScript(String script, String activeStyleName, Int32 tabSize, ITextReplacer textWriter, Int32 startIndex)
    15 Mar 2018 16:36:27,345 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:27,346 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:27,348 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:27,348 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:27,349 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 140401580
    15 Mar 2018 16:36:27,350 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:27,351 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:29,561 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:29,564 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:29,567 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 140401580

    jgonnering
    0
  • jgonnering
    getting errors with this version formatting SQL (CTRL+K, CTRL+Y). I do not get error using default formatting style. Email me for SQL being formatted and format file.
    from log file with verbose logging:
    09 Mar 2018 21:07:21,011 [35] WARN RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2 - Error at range (0:1): System.ArgumentNullException: Value cannot be null.
    Parameter name: token
    at Format.Engine.Formatting.RuleCollection.Rules.VerticallyLinkedTokensRuleExtensions.AlignmentPoint(VerticallyLinkedTokensRule verticallyLinkedTokensRule, TSqlParserToken token)
    at Format.Engine.Formatting.RuleProviders.Common.ProcedureParameterRuleProvider.AlignTo(IList`1 procedureParameters, TSqlFragment parentFragment)
    at Format.Engine.Formatting.RuleProviders.CreateAlterProcedureRuleProvider.FormatProcedureStatementBody(ProcedureStatementBody fragment)
    at Format.Engine.Formatting.RuleProviders.Visitors.TopLevelVisitor.Visit(AlterProcedureStatement fragment)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(AlterProcedureStatement node)
    at Microsoft.SqlServer.TransactSql.ScriptDom.AlterProcedureStatement.Accept(TSqlFragmentVisitor visitor)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlBatch.AcceptChildren(TSqlFragmentVisitor visitor)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(TSqlBatch node)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlBatch.Accept(TSqlFragmentVisitor visitor)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlScript.AcceptChildren(TSqlFragmentVisitor visitor)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(TSqlScript node)
    at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlScript.Accept(TSqlFragmentVisitor visitor)
    at Format.Engine.Formatting.SqlFormatter.Format(String script, IScriptWriter scriptWriter, CancellationToken cancellationToken, IList`1& parseErrors)
    at RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2.FormatScript(String script, LayoutOptions activeStyle, Int32 tabSize, IList`1& formatErrors, CancellationToken cancellationToken)
    at RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2.FormatScript(String script, String activeStyleName, Int32 tabSize, ITextReplacer textWriter, Int32 startIndex)
    09 Mar 2018 21:07:23,048 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    09 Mar 2018 21:07:23,049 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    09 Mar 2018 21:07:23,050 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    09 Mar 2018 21:07:23,051 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    09 Mar 2018 21:07:23,052 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 118660780
    09 Mar 2018 21:07:23,053 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    09 Mar 2018 21:07:23,054 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    09 Mar 2018 21:07:25,443 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    09 Mar 2018 21:07:25,447 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    09 Mar 2018 21:07:25,450 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 118660780
    jgonnering
    0
  • jgonnering
    still getting errors using the March 14th release trying to format using a custom style.

    15 Mar 2018 16:36:19,679 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:19,680 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:19,682 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:19,683 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:19,683 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 140401580
    15 Mar 2018 16:36:19,684 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:19,685 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:25,677 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:25,677 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:25,678 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 140401580
    15 Mar 2018 16:36:25,708 [107] WARN  RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2 - Error at range (0:1): System.ArgumentNullException: Value cannot be null.
    Parameter name: token
       at Format.Engine.Formatting.RuleCollection.Rules.VerticallyLinkedTokensRuleExtensions.AlignmentPoint(VerticallyLinkedTokensRule verticallyLinkedTokensRule, TSqlParserToken token)
       at Format.Engine.Formatting.RuleProviders.Common.ProcedureParameterRuleProvider.AlignTo(IList`1 procedureParameters, TSqlFragment parentFragment)
       at Format.Engine.Formatting.RuleProviders.CreateAlterProcedureRuleProvider.FormatProcedureStatementBody(ProcedureStatementBody fragment)
       at Format.Engine.Formatting.RuleProviders.Visitors.TopLevelVisitor.Visit(AlterProcedureStatement fragment)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(AlterProcedureStatement node)
       at Microsoft.SqlServer.TransactSql.ScriptDom.AlterProcedureStatement.Accept(TSqlFragmentVisitor visitor)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlBatch.AcceptChildren(TSqlFragmentVisitor visitor)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(TSqlBatch node)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlBatch.Accept(TSqlFragmentVisitor visitor)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlScript.AcceptChildren(TSqlFragmentVisitor visitor)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(TSqlScript node)
       at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlScript.Accept(TSqlFragmentVisitor visitor)
       at Format.Engine.Formatting.SqlFormatter.Format(String script, IScriptWriter scriptWriter, CancellationToken cancellationToken, IList`1& parseErrors)
       at RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2.FormatScript(String script, LayoutOptions activeStyle, Int32 tabSize, IList`1& formatErrors, CancellationToken cancellationToken)
       at RedGate.SqlPrompt.ScriptDomInterop.Format.FormatterV2.FormatScript(String script, String activeStyleName, Int32 tabSize, ITextReplacer textWriter, Int32 startIndex)
    15 Mar 2018 16:36:27,345 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:27,346 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:27,348 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:27,348 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:27,349 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 140401580
    15 Mar 2018 16:36:27,350 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:27,351 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:29,561 [1] DEBUG UniversalAuthenticationAccessTokenCache - Authentication cache not available, returning null
    15 Mar 2018 16:36:29,564 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
    15 Mar 2018 16:36:29,567 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 140401580

    jgonnering
    0
  • RichardL
    Hi @jgonnering

    Thanks for your posts and info. 


    This looks like a question that one of Support engineers will need to investigate for you.

     

    If you have a support contract, please send us a ticket. Provide as much information as you can - screenshots of any errors, log files etc – so we can help you as fast as possible.

    If you're not covered by a Support contract at the moment, email our Sales team at sales@red-gate.com, and they'll be able to help. 

    RichardL
    0
  • Michael Clark
    Hi @jgonnering,

    Thanks for your above logs, we reproduced the problem and it should be fixed as of SQL Prompt 9.1.4:

    You can download SQL Prompt 9.1.4 here:
    http://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_9.1.4.4532.exe

    Please let us know if you experience any more issues.

    Best,
    Michael
    Michael Clark
    0

Add comment

Please sign in to leave a comment.