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

SQL Format doesn't format function

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
);
sdks
0

Comments

6 comments

Sort by
  • 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
    krzysztofkroczak
    0
  • 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)
    sdks
    0
  • Ngainer
    This is the same error I am receiving on the other thread "Error laying out SQL".
    Ngainer
    0
  • 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
    0
  • sdks
    fixed with the latest version
    sdks
    0
  • 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
    Michael Clark
    0

Add comment

Please sign in to leave a comment.