Options

Error laying out SQL

torsten.strausstorsten.strauss Posts: 266 Silver 3
in SQL Prompt
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
Tagged:
· Share on Twitter

Answers

  • Options
    MendedSlinkyMendedSlinky Posts: 2 New member
    I get a similar error, and it highlights the first character of the script and says "Value cannot be null. Paramater name: token"
    · Share on Twitter
  • Options
    NgainerNgainer Posts: 16 Bronze 3
    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
    · Share on Twitter
  • Options
    NgainerNgainer Posts: 16 Bronze 3
    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.
    · Share on Twitter
  • Options
    MendedSlinkyMendedSlinky Posts: 2 New member
    This didn't start happening until I updated to 9.1.2.4363
    · Share on Twitter
  • Options
    bauerjubauerju Posts: 33 Bronze 3
    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
    · Share on Twitter
  • Options
    NgainerNgainer Posts: 16 Bronze 3
    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.
    · Share on Twitter
  • Options
    JimFJimF Posts: 49 Bronze 3
    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?

    · Share on Twitter
  • Options
    bauerjubauerju Posts: 33 Bronze 3
    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
    · Share on Twitter
  • Options
    JimFJimF Posts: 49 Bronze 3
    @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...)
    · Share on Twitter
  • Options
    FredericoJFredericoJ Posts: 34 Bronze 3
    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
    Software Engineer
    Redgate Software
    · Share on Twitter
  • Options
    bauerjubauerju Posts: 33 Bronze 3
    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
    · Share on Twitter
Sign In or Register to comment.