SQL Format doesn't format function

sdks

I have latest SQL Prompt - 9.1.2.4363 (07th March). Following function can't be formatted and the error "Error laying out sql" is returned. Tried on both SSMS (v17.5) and VS 2017 (15.5.3)

CREATE FUNCTION [dbo].[test](@a CHAR(8))
RETURNS TABLE AS RETURN
(
    SELECT TOP (1) 1 AS one
);

krzysztofkroczak

Hi @sdks,

Thanks for letting us know.

We've tried to replicate the issue locally but so far we've been unable to.

Would it be possible for you to send us the formatting style and log file (either on the forums or by emailing it to us at sqlpromptteam@red-gate.com) that is throwing up the problem?

Here is description how to find logs: https://documentation.red-gate.com/sp8/troubleshooting/finding-the-sql-prompt-log-files

Best regards,
Krzysztof

sdks

12 Mar 2018 16:23:46,610 [1] INFO  RedGate.SQLQueryKeeper.TabWatcher - Document opened
12 Mar 2018 16:23:47,162 [1] INFO  RedGate.SqlPrompt.Cache.Candidates.DatabaseCandidateBase - [db_instance].[db_name] loading skipped
12 Mar 2018 16:23:48,974 [58] 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.CreateAlterFunctionRuleProvider.ProvideForCommonFunction(TSqlFragment fragment, TSqlParserToken firstToken, TSqlParserToken asToken, TSqlFragment returnFragment, IList`1 parameters)
   at Format.Engine.Formatting.RuleProviders.CreateAlterFunctionRuleProvider.FormatFunctionStatementBody(FunctionStatementBody fragment)
   at Format.Engine.Formatting.RuleProviders.Visitors.TopLevelVisitor.Visit(CreateFunctionStatement fragment)
   at Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(CreateFunctionStatement node)
   at Microsoft.SqlServer.TransactSql.ScriptDom.CreateFunctionStatement.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)

Ngainer

This is the same error I am receiving on the other thread "Error laying out SQL".

sdks

These are the style settings I use:

<?xml version="1.0" encoding="utf-8"?>
<LayoutOptions xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Format.Engine.Formatting.Options">
  <AlignCaseElseToWhen>true</AlignCaseElseToWhen>
  <AlignToTab>false</AlignToTab>
  <BetweenAndAlignment>ToBetween</BetweenAndAlignment>
  <BooleanOperatorAlignment>RightAlignedToStatement</BooleanOperatorAlignment>
  <BooleanOperatorBreakType>ChopIfLong</BooleanOperatorBreakType>
  <BreakAssignmentIfLong>false</BreakAssignmentIfLong>
  <BreakOnConstraints>true</BreakOnConstraints>
  <BuiltInDataTypeCasing>Uppercase</BuiltInDataTypeCasing>
  <BuiltInFunctionCasing>Uppercase</BuiltInFunctionCasing>
  <CaseEndAlignment>ToCase</CaseEndAlignment>
  <CaseExpressionAlignment>Tabbed</CaseExpressionAlignment>
  <CaseWhenElseAlignment>ToFirstItem</CaseWhenElseAlignment>
  <ClauseAlignment>RightAlignedToStatement</ClauseAlignment>
  <ClauseIndentation>0</ClauseIndentation>
  <ClosingParenthesisAlignment>ToOpeningBracket</ClosingParenthesisAlignment>
  <CollapseCaseExpressionIfShort>true</CollapseCaseExpressionIfShort>
  <CollapseCaseIfShortCharacterCount>75</CollapseCaseIfShortCharacterCount>
  <CollapseIfShortControlFlowCharacterCount>78</CollapseIfShortControlFlowCharacterCount>
  <CollapseIfShortDdlCharacterCount>75</CollapseIfShortDdlCharacterCount>
  <CollapseIfShortDmlCharacterCount>75</CollapseIfShortDmlCharacterCount>
  <CollapseIfShortSubqueryCharacterCount>55</CollapseIfShortSubqueryCharacterCount>
  <CollapseShortControlFlowStatements>true</CollapseShortControlFlowStatements>
  <CollapseShortDdlStatements>true</CollapseShortDdlStatements>
  <CollapseShortDmlStatements>true</CollapseShortDmlStatements>
  <CollapseShortSubqueries>true</CollapseShortSubqueries>
  <CommaAlignment>ToList</CommaAlignment>
  <ConstraintColumnsBreakType>ChopIfLong</ConstraintColumnsBreakType>
  <CreateAlterClosingParenthesisAlignment>ToClause</CreateAlterClosingParenthesisAlignment>
  <CreateAlterIndentParenthesisedExpression>true</CreateAlterIndentParenthesisedExpression>
  <CreateAlterOpeningParenthesisAlignment>ToStatement</CreateAlterOpeningParenthesisAlignment>
  <CreateAlterOpeningParenthesisBreakType>ChopAlways</CreateAlterOpeningParenthesisBreakType>
  <CreateAlterParenthesisedExpressionBreakType>Never</CreateAlterParenthesisedExpressionBreakType>
  <CreateAlterPlaceClosingParenthesisOnNewLine>true</CreateAlterPlaceClosingParenthesisOnNewLine>
  <CreateTableBreakBeforeFirstListItem>true</CreateTableBreakBeforeFirstListItem>
  <CteAsAlignment>RightAlignedToStatement</CteAsAlignment>
  <CteColumnsOpeningParenthesisAlignment>Tabbed</CteColumnsOpeningParenthesisAlignment>
  <CteColumnsOpeningParenthesisBreakType>ChopAlways</CteColumnsOpeningParenthesisBreakType>
  <CteContentsOpeningParenthesisAlignment>ToStatement</CteContentsOpeningParenthesisAlignment>
  <CteContentsOpeningParenthesisBreakType>Never</CteContentsOpeningParenthesisBreakType>
  <CteContentsParenthesisedExpressionAlignment>ToStartOfOpeningBracket</CteContentsParenthesisedExpressionAlignment>
  <CteContentsParenthesisedExpressionBreakType>ChopIfLong</CteContentsParenthesisedExpressionBreakType>
  <FirstCreateAlterDefintionBreakType>ChopIfLongOrMultipleItems</FirstCreateAlterDefintionBreakType>
  <FirstListItemBreakType>Never</FirstListItemBreakType>
  <FromClauseFirstItemBreakType>Never</FromClauseFirstItemBreakType>
  <FunctionArgumentsBreakType>ChopIfLong</FunctionArgumentsBreakType>
  <GlobalVariablesCasing>LeaveAsIs</GlobalVariablesCasing>
  <GroupByOrderByFirstItemBreakType>Never</GroupByOrderByFirstItemBreakType>
  <InValuesFirstItemBreakType>SimpleWrap</InValuesFirstItemBreakType>
  <InValuesOpeningParenthesisAlignment>ToStatement</InValuesOpeningParenthesisAlignment>
  <InValuesOpeningParenthesisBreakType>Never</InValuesOpeningParenthesisBreakType>
  <InValuesSubsequentListItemsBreakType>SimpleWrap</InValuesSubsequentListItemsBreakType>
  <IndentBeginEndKeywords>true</IndentBeginEndKeywords>
  <IndentBlockContents>true</IndentBlockContents>
  <IndentCteContents>false</IndentCteContents>
  <IndentCteName>false</IndentCteName>
  <IndentDdlClauses>true</IndentDdlClauses>
  <IndentJoinTable>false</IndentJoinTable>
  <IndentListItems>true</IndentListItems>
  <IndentParenthesisedExpression>true</IndentParenthesisedExpression>
  <InsertEmptyLineBetweenJoins>false</InsertEmptyLineBetweenJoins>
  <IsMigratedStyle>false</IsMigratedStyle>
  <JoinBreakType>ChopAlways</JoinBreakType>
  <JoinClauseAlignment>RightAlignedToStatement</JoinClauseAlignment>
  <JoinConditionBreakType>Never</JoinConditionBreakType>
  <JoinOnAlignment>ToJoin</JoinOnAlignment>
  <JoinOnConditionAlignment>ToOnKeyword</JoinOnConditionAlignment>
  <Name>Timepayment</Name>
  <NewLineAfterTopRowFilter>false</NewLineAfterTopRowFilter>
  <NewLineBeforeTopRowFilter>false</NewLineBeforeTopRowFilter>
  <NewLineForCorrelatedTableSource>true</NewLineForCorrelatedTableSource>
  <NumberOfEmptyLinesAfterBatchSeparator>1</NumberOfEmptyLinesAfterBatchSeparator>
  <NumberOfEmptyLinesBetweenStatements>1</NumberOfEmptyLinesBetweenStatements>
  <NumberOfSpacesInTab>4</NumberOfSpacesInTab>
  <OpeningParenthesisAlignment>RightAlignedToStatement</OpeningParenthesisAlignment>
  <OpeningParenthesisBreakType>Never</OpeningParenthesisBreakType>
  <OptionsVersion>10</OptionsVersion>
  <OverrideParenthesesOptionsForCreateAlterStatements>true</OverrideParenthesesOptionsForCreateAlterStatements>
  <ParenthesisedExpressionAlignment>TabbedFromOpeningBracket</ParenthesisedExpressionAlignment>
  <ParenthesisedExpressionBreakType>ChopIfLong</ParenthesisedExpressionBreakType>
  <PlaceBeginOnNewLine>true</PlaceBeginOnNewLine>
  <PlaceCaseElseOnNewLine>true</PlaceCaseElseOnNewLine>
  <PlaceCaseEndOnNewLine>true</PlaceCaseEndOnNewLine>
  <PlaceCaseExpressionOnNewLine>false</PlaceCaseExpressionOnNewLine>
  <PlaceCaseFirstWhenOnNewLine>IfTheresAnInputExpression</PlaceCaseFirstWhenOnNewLine>
  <PlaceClosingParenthesisOnNewLine>true</PlaceClosingParenthesisOnNewLine>
  <PlaceCommasBeforeListItems>true</PlaceCommasBeforeListItems>
  <PlaceJoinOnKeywordOnANewLine>false</PlaceJoinOnKeywordOnANewLine>
  <PlaceJoinTableOnNewLine>false</PlaceJoinTableOnNewLine>
  <PreferBreakBeforeAsKeyword>false</PreferBreakBeforeAsKeyword>
  <PreferBreakBeforeBetweenAndKeyword>false</PreferBreakBeforeBetweenAndKeyword>
  <PreferBreakBeforeBetweenKeyword>false</PreferBreakBeforeBetweenKeyword>
  <PreferBreakBeforeConditionOperator>true</PreferBreakBeforeConditionOperator>
  <PreferBreakBeforeCteName>false</PreferBreakBeforeCteName>
  <PreferBreakBeforeEquals>false</PreferBreakBeforeEquals>
  <PreferBreakBeforeInsertTable>false</PreferBreakBeforeInsertTable>
  <PreferBreakBeforeThenKeyword>false</PreferBreakBeforeThenKeyword>
  <PreserveLineSpacingBetweenStatements>false</PreserveLineSpacingBetweenStatements>
  <PreserveLineSpacingWithinStatements>false</PreserveLineSpacingWithinStatements>
  <ReservedKeywordsCasing>Uppercase</ReservedKeywordsCasing>
  <SemicolonWhitespace>None</SemicolonWhitespace>
  <SpaceBeforeUnits>false</SpaceBeforeUnits>
  <SpaceBetweenDataTypeAndParameters>false</SpaceBetweenDataTypeAndParameters>
  <SpaceInsideEmptyParentheses>false</SpaceInsideEmptyParentheses>
  <SpacesAroundArithmeticOperators>
    <m_After>true</m_After>
    <m_Before>true</m_Before>
  </SpacesAroundArithmeticOperators>
  <SpacesAroundCommas>
    <m_After>true</m_After>
    <m_Before>false</m_Before>
  </SpacesAroundCommas>
  <SpacesAroundComparisonOperators>
    <m_After>true</m_After>
    <m_Before>true</m_Before>
  </SpacesAroundComparisonOperators>
  <SpacesAroundFunctionCallArguments>
    <m_After>false</m_After>
    <m_Before>false</m_Before>
  </SpacesAroundFunctionCallArguments>
  <SpacesAroundFunctionCalls>
    <m_After>false</m_After>
    <m_Before>false</m_Before>
  </SpacesAroundFunctionCalls>
  <SpacesAroundInPredicateContents>
    <m_After>false</m_After>
    <m_Before>false</m_Before>
  </SpacesAroundInPredicateContents>
  <SpacesAroundParentheses>
    <m_After>true</m_After>
    <m_Before>true</m_Before>
  </SpacesAroundParentheses>
  <SpacesAroundParenthesesContents>
    <m_After>false</m_After>
    <m_Before>false</m_Before>
  </SpacesAroundParenthesesContents>
  <SpacesOrTabs>OnlySpaces</SpacesOrTabs>
  <SubsequentListItemsBreakType>ChopAlways</SubsequentListItemsBreakType>
  <UseGlobalListOptionsForDmlStatements>false</UseGlobalListOptionsForDmlStatements>
  <UseObjectDefinitionCase>true</UseObjectDefinitionCase>
  <VerticallyAlignAliases>true</VerticallyAlignAliases>
  <VerticallyAlignClauseItems>true</VerticallyAlignClauseItems>
  <VerticallyAlignColumnDefinitions>true</VerticallyAlignColumnDefinitions>
  <VerticallyAlignComparisonOperators>true</VerticallyAlignComparisonOperators>
  <VerticallyAlignDataTypes>true</VerticallyAlignDataTypes>
  <VerticallyAlignJoinConditionWithJoinTable>false</VerticallyAlignJoinConditionWithJoinTable>
  <VerticallyAlignListItemComments>true</VerticallyAlignListItemComments>
  <VerticallyAlignListItems>true</VerticallyAlignListItems>
  <WhereClauseFirstItemBreakType>Never</WhereClauseFirstItemBreakType>
  <WrapColumn>120</WrapColumn>
  <WrapLongLines>true</WrapLongLines>
</LayoutOptions>

sdks

fixed with the latest version

Michael Clark

Hi @Ngainer

As @sdks mentioned this issue 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

Best,
Michael