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

Error laying out SQL

Hi Redgate Team,

formatting the following code will cause the error "Error laying out SQL"
The S from Select is highlighted and the error Object reference not set to an instance of an object. 
SELECT
	cus.CustomerAlternateKey AS CustomerNumber
,	pro.ProductAlternateKey AS ProductNumber
,	pro.EnglishProductName AS ProductName
,	SUM(fisb.OrderQuantity) AS TotalOrderQuantity
,	SUM(fisb.DiscountAmount) AS TotalDiscountAmount
FROM
	dbo.FactInternetSalesBig AS fisb
INNER JOIN
	DimProduct AS pro
ON
	pro.ProductKey = fisb.ProductKey
INNER JOIN
	DimCustomer AS cus
ON
	cus.CustomerKey = fisb.CustomerKey
GROUP BY
	cus.CustomerAlternateKey, pro.ProductAlternateKey, pro.EnglishProductName
	OPTION(IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);
GO

Thanks for fixing this!

Regards
Torsten
torsten.strauss
0

Comments

11 comments

Sort by
  • MendedSlinky
    I get a similar error, and it highlights the first character of the script and says "Value cannot be null. Paramater name: token"
    MendedSlinky
    0
  • Ngainer
    I am getting the same error: lp3r2t728wgl.png
    It seems to happen on CREATE or ALTER scripts. If I change the above code to a DECLARE statement instead of a CREATE or ALTER PROCEDURE then it formats fine.

    d564f3dzsrol.png
    Ngainer
    0
  • Ngainer
    The issue seams to be happening on Formatting Styles that are user-defined. If I change to one of the REDGATE STYLES in the "Formatting styles" window then the code is formatted correctly.
    Ngainer
    0
  • MendedSlinky
    This didn't start happening until I updated to 9.1.2.4363
    MendedSlinky
    0
  • bauerju
    Same bug here. Latest Release 9.1.2.4363

    Here is part of the log. (Verbose Logging Enabled)
    09 Mar 2018 07:20:40,473 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:40,474 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:40,474 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 688490668
09 Mar 2018 07:20:40,475 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:41,948 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:41,950 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 688490668
09 Mar 2018 07:20:42,181 [85] 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)

    Jürgen
    bauerju
    0
  • Ngainer
    I believe I have found the option that is causing the bug. The option "Align data types and constraints" in the "Schema (DDL)" group seems to cause the provided styles to fail. If I uncheck this option on any of my custom styles, the error goes away.
    Ngainer
    0
  • JimF
    I just upgraded to 9.1.3.4467 today and I can no longer do "Format SQL" for a CREATE or ALTER PROCEDURE. (not sure if other statements cause it to fail).

        SQL Prompt was unable to complete this operation.
        Problem areas have been highlighted.
       
        Errors during parsing the script:
        Ln: 1 Col: 1 - Value cannot be null.
        Parameter name: token

    I now see that this issues has been around for a few days. Is there a fix coming out soon?

    JimF
    0
  • bauerju
    Hi
    Yes, update from today to 9.1.3.4467 has same problem. But there is now a nice popup with the failure.

    Btw I opened a support call and this is logged as SP-6865  in their bug tracking system.

    Jürgen
    bauerju
    0
  • JimF
    @bauerju Is there a way of tracking this bug? That is, is there a public facing side to their bug tracking system? This one is a huge issue with me and I would like to keep a close eye on it's fix. If I would have know it existed, I would not have upgraded yet (and it's a pain to downgrade SQL Prompt...)
    JimF
    0
  • FredericoJ
    Hi @MendedSlinky, @Ngainer, @bauerju and @JimF

    Apologies for the issue you are experiencing and thanks for reporting it.

    We are currently investigating the problem (which we can reproduce locally) and hope to have a fix out soon. We will let you know as soon as it is available.

    Kind regards,

    Frederico
    FredericoJ
    0
  • bauerju
    Same bug here. Latest Release 9.1.2.4363

    Here is part of the log. (Verbose Logging Enabled)
    09 Mar 2018 07:20:40,473 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:40,474 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:40,474 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 688490668
09 Mar 2018 07:20:40,475 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:41,948 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:41,950 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 688490668
09 Mar 2018 07:20:42,181 [85] 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 07:20:57,947 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:57,948 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:20:57,949 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 688490668
09 Mar 2018 07:20:57,949 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:03,355 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:03,356 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 688490668
09 Mar 2018 07:21:05,490 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:05,492 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:05,493 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:05,493 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 688490668
09 Mar 2018 07:21:05,494 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:11,989 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:11,990 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 688490668
09 Mar 2018 07:21:14,489 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:14,491 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:14,492 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 688490668
09 Mar 2018 07:21:14,492 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:17,358 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:17,359 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 688490668
09 Mar 2018 07:21:18,827 [74] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading EngineOptions from C:\Users\BAUERJU\AppData\Local\Red Gate\SQL Prompt 9\RedGate_SqlPrompt_Engine_EngineOptions.xml (attempt 0 of 10)...
09 Mar 2018 07:21:18,829 [74] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded EngineOptions
09 Mar 2018 07:21:18,831 [74] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading EngineOptions from C:\Users\BAUERJU\AppData\Local\Red Gate\SQL Prompt 9\RedGate_SqlPrompt_Engine_EngineOptions.xml (attempt 0 of 10)...
09 Mar 2018 07:21:18,831 [59] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading UIOptions from C:\Users\BAUERJU\AppData\Local\Red Gate\SQL Prompt 9\RedGate_SQLPrompt_CommonUI_Options_UIOptions.xml (attempt 0 of 10)...
09 Mar 2018 07:21:18,832 [74] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded EngineOptions
09 Mar 2018 07:21:18,832 [59] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded UIOptions
09 Mar 2018 07:21:18,838 [77] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading LayoutOptions from C:\Users\BAUERJU\AppData\Local\Red Gate\SQL Prompt 9\RedGate_SqlPrompt_Format_Layout_LayoutOptions.xml (attempt 0 of 10)...
09 Mar 2018 07:21:18,838 [78] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Loading TabMagicOptions from C:\Users\BAUERJU\AppData\Local\Red Gate\SQL Prompt 9\RedGate_SQLPrompt_CommonUI_Options_TabMagicOptions.xml (attempt 0 of 10)...
09 Mar 2018 07:21:18,839 [77] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded LayoutOptions
09 Mar 2018 07:21:18,839 [78] INFO  RedGate.SqlPrompt.Metadata.Options.OptionsBase - Successfully loaded TabMagicOptions
09 Mar 2018 07:21:18,849 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:18,850 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:18,851 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnSetFocus IVsTextView: 688490668
09 Mar 2018 07:21:18,851 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:26,068 [1] DEBUG UniversalAuthenticationAccessTokenCache - No access tokens in cache, returning empty collection
09 Mar 2018 07:21:26,069 [1] DEBUG RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor - OnKillFocus IVsTextView: 688490668

    Jürgen
    bauerju
    0

Add comment

Please sign in to leave a comment.