Error laying out SQL

torsten.strauss · February 3, 2018 2:28PM

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

MendedSlinky · March 8, 2018 9:57PM

I get a similar error, and it highlights the first character of the script and says "Value cannot be null. Paramater name: token"

Ngainer · March 9, 2018 2:54PM

I am getting the same error:

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.

Ngainer · March 9, 2018 5:47PM

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.

MendedSlinky · March 9, 2018 5:48PM

This didn't start happening until I updated to 9.1.2.4363

bauerju · March 13, 2018 11:05AM

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

Ngainer · March 13, 2018 2:10PM

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.

Image: https://us.v-cdn.net/6029854/uploads/editor/y1/ri7128ava5jb.png

Image: https://us.v-cdn.net/6029854/uploads/editor/3b/gpgjapioa4pv.png

JimF · March 14, 2018 10:14PM

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?

bauerju · March 15, 2018 5:08AM

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

JimF · March 15, 2018 4:08PM

@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...)

FredericoJ · March 15, 2018 5:13PM

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

bauerju · March 19, 2018 11:54AM

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

Error laying out SQL

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk