What are the challenges you face when working across database platforms? Take the survey
Options

SQL Format doesn't format function

sdkssdks Posts: 44 Bronze 2
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
);
Tagged:

Answers

  • Options
    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
  • Options
    sdkssdks Posts: 44 Bronze 2
    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)
    
  • Options
    NgainerNgainer Posts: 16 Bronze 3
    This is the same error I am receiving on the other thread "Error laying out SQL".
  • Options
    sdkssdks Posts: 44 Bronze 2
    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>
    
  • Options
    sdkssdks Posts: 44 Bronze 2
    fixed with the latest version
  • Options
    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
    Michael Clark
    Technical Lead, SQL Monitor
Sign In or Register to comment.