SQL Prompt 10 - Import Syle from 9

DSQU

Hello- i have upgraded to the latest version of SQL prompt however I cannot open a style from SQL Prompt 9. In my folder: C:\Users\XXXX\AppData\Local\Red Gate\SQL Prompt 10\Styles I have a file with the type: SQLPROMPTSTYLEV2 File, which looks like its from when I used version 9. I also have a new custom file type that I just created, that JSON file type. 

From this other post: https://forum.red-gate.com/discussion/87658/restore-style-in-sqlprompt-v-10-from-sqlprompt-v-9
and this documentation: https://documentation.red-gate.com/sp/sql-code-formatting-and-styles/updating-style-files-from-before-version-10-5

I don't have a StylesV2-backup folder in my Styles folder so I can't do what is described. 

Note that I look at the about for my prompt version and it shows 10.11.12.2700, but i also see 10.5 version mentioned. I downloaded the latest version and it says SQL prompt is already upgraded. 

Please advise 

Kurt_M

Hi @DSQU,

Would you be happy sharing the old version 9 file with me on the forum so that I may take a look at it from my end?

DSQU

Sure - it won't let me attach the file type but i have pasted the contents of the "SQLPROMPTSTYLEV2 File" type: 

<?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>AsPartOfList</BooleanOperatorAlignment>

  <BooleanOperatorBreakType>ChopAlways</BooleanOperatorBreakType>

  <BreakAssignmentIfLong>true</BreakAssignmentIfLong>

  <BreakOnConstraints>true</BreakOnConstraints>

  <BuiltInDataTypeCasing>Uppercase</BuiltInDataTypeCasing>

  <BuiltInFunctionCasing>Uppercase</BuiltInFunctionCasing>

  <CaseEndAlignment>ToCase</CaseEndAlignment>

  <CaseExpressionAlignment>Tabbed</CaseExpressionAlignment>

  <CaseWhenElseAlignment>Tabbed</CaseWhenElseAlignment>

  <ClauseAlignment>ToStatement</ClauseAlignment>

  <ClauseIndentation>0</ClauseIndentation>

  <ClosingParenthesisAlignment>ToOpeningBracket</ClosingParenthesisAlignment>

  <CollapseCaseExpressionIfShort>false</CollapseCaseExpressionIfShort>

  <CollapseCaseIfShortCharacterCount>75</CollapseCaseIfShortCharacterCount>

  <CollapseIfShortControlFlowCharacterCount>78</CollapseIfShortControlFlowCharacterCount>

  <CollapseIfShortDdlCharacterCount>75</CollapseIfShortDdlCharacterCount>

  <CollapseIfShortDmlCharacterCount>75</CollapseIfShortDmlCharacterCount>

  <CollapseIfShortParenthesesContentsCharacterCount>75</CollapseIfShortParenthesesContentsCharacterCount>

  <CollapseIfShortSubqueryCharacterCount>75</CollapseIfShortSubqueryCharacterCount>

  <CollapseShortControlFlowStatements>false</CollapseShortControlFlowStatements>

  <CollapseShortDdlStatements>true</CollapseShortDdlStatements>

  <CollapseShortDmlStatements>false</CollapseShortDmlStatements>

  <CollapseShortParenthesesContents>false</CollapseShortParenthesesContents>

  <CollapseShortSubqueries>true</CollapseShortSubqueries>

  <CommaAlignment>ToList</CommaAlignment>

  <ConstraintColumnsBreakType>ChopIfLongOrMultipleItems</ConstraintColumnsBreakType>

  <CreateAlterClosingParenthesisAlignment>ToOpeningBracket</CreateAlterClosingParenthesisAlignment>

  <CreateAlterIndentParenthesisedExpression>true</CreateAlterIndentParenthesisedExpression>

  <CreateAlterOpeningParenthesisAlignment>ToStatement</CreateAlterOpeningParenthesisAlignment>

  <CreateAlterOpeningParenthesisBreakType>ChopAlways</CreateAlterOpeningParenthesisBreakType>

  <CreateAlterParenthesisLayout>ExpandedToStatement</CreateAlterParenthesisLayout>

  <CreateAlterParenthesisedExpressionBreakType>ChopAlways</CreateAlterParenthesisedExpressionBreakType>

  <CreateAlterPlaceClosingParenthesisOnNewLine>true</CreateAlterPlaceClosingParenthesisOnNewLine>

  <CreateTableBreakBeforeFirstListItem>true</CreateTableBreakBeforeFirstListItem>

  <CteAsAlignment>ToStatement</CteAsAlignment>

  <CteColumnsOpeningParenthesisAlignment>ToStatement</CteColumnsOpeningParenthesisAlignment>

  <CteColumnsOpeningParenthesisBreakType>Never</CteColumnsOpeningParenthesisBreakType>

  <CteContentsOpeningParenthesisAlignment>ToStatement</CteContentsOpeningParenthesisAlignment>

  <CteContentsOpeningParenthesisBreakType>Never</CteContentsOpeningParenthesisBreakType>

  <CteContentsParenthesisLayout>CompactSimple</CteContentsParenthesisLayout>

  <CteContentsParenthesisedExpressionAlignment>ToStartOfOpeningBracket</CteContentsParenthesisedExpressionAlignment>

  <CteContentsParenthesisedExpressionBreakType>Never</CteContentsParenthesisedExpressionBreakType>

  <FirstCreateAlterDefintionBreakType>ChopIfLongOrMultipleItems</FirstCreateAlterDefintionBreakType>

  <FirstCreateProcedureParamBreakType>ChopIfLongOrMultipleItems</FirstCreateProcedureParamBreakType>

  <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>false</IndentBeginEndKeywords>

  <IndentBlockContents>true</IndentBlockContents>

  <IndentCteContents>false</IndentCteContents>

  <IndentCteName>false</IndentCteName>

  <IndentDdlClauses>false</IndentDdlClauses>

  <IndentInsertColumnListsParenthesisedExpression>true</IndentInsertColumnListsParenthesisedExpression>

  <IndentInsertValuesListsParenthesisedExpression>true</IndentInsertValuesListsParenthesisedExpression>

  <IndentJoinTable>false</IndentJoinTable>

  <IndentListItems>true</IndentListItems>

  <IndentParenthesisedExpression>true</IndentParenthesisedExpression>

  <InsertColumnListParenthesisLayout>ExpandedToStatement</InsertColumnListParenthesisLayout>

  <InsertEmptyLineBetweenJoins>false</InsertEmptyLineBetweenJoins>

  <InsertValuesListParenthesisLayout>CompactToStatement</InsertValuesListParenthesisLayout>

  <IsMigratedStyle>false</IsMigratedStyle>

  <JoinBreakType>ChopAlways</JoinBreakType>

  <JoinClauseAlignment>Tabbed</JoinClauseAlignment>

  <JoinConditionBreakType>Never</JoinConditionBreakType>

  <JoinOnAlignment>TabbedFromJoin</JoinOnAlignment>

  <JoinOnConditionAlignment>ToOnKeyword</JoinOnConditionAlignment>

  <Name>Custom style</Name>

  <NewLineAfterTopRowFilter>true</NewLineAfterTopRowFilter>

  <NewLineBeforeTopRowFilter>false</NewLineBeforeTopRowFilter>

  <NewLineForCorrelatedTableSource>true</NewLineForCorrelatedTableSource>

  <NumberOfEmptyLinesAfterBatchSeparator>1</NumberOfEmptyLinesAfterBatchSeparator>

  <NumberOfEmptyLinesBetweenStatements>1</NumberOfEmptyLinesBetweenStatements>

  <NumberOfSpacesInTab>4</NumberOfSpacesInTab>

  <OpeningParenthesisAlignment>ToStatement</OpeningParenthesisAlignment>

  <OpeningParenthesisBreakType>ChopAlways</OpeningParenthesisBreakType>

  <OptionsVersion>12</OptionsVersion>

  <OverrideParenthesesOptionsForCreateAlterStatements>true</OverrideParenthesesOptionsForCreateAlterStatements>

  <ParenthesisLayout>ExpandedToStatement</ParenthesisLayout>

  <ParenthesisedExpressionAlignment>TabbedFromOpeningBracket</ParenthesisedExpressionAlignment>

  <ParenthesisedExpressionBreakType>ChopAlways</ParenthesisedExpressionBreakType>

  <PlaceBeginOnNewLine>true</PlaceBeginOnNewLine>

  <PlaceCaseElseOnNewLine>true</PlaceCaseElseOnNewLine>

  <PlaceCaseEndOnNewLine>true</PlaceCaseEndOnNewLine>

  <PlaceCaseExpressionOnNewLine>true</PlaceCaseExpressionOnNewLine>

  <PlaceCaseFirstWhenOnNewLine>Always</PlaceCaseFirstWhenOnNewLine>

  <PlaceClosingParenthesisOnNewLine>true</PlaceClosingParenthesisOnNewLine>

  <PlaceCommasBeforeListItems>true</PlaceCommasBeforeListItems>

  <PlaceJoinOnKeywordOnANewLine>true</PlaceJoinOnKeywordOnANewLine>

  <PlaceJoinTableOnNewLine>false</PlaceJoinTableOnNewLine>

  <PreferBreakBeforeAsKeyword>true</PreferBreakBeforeAsKeyword>

  <PreferBreakBeforeBetweenAndKeyword>false</PreferBreakBeforeBetweenAndKeyword>

  <PreferBreakBeforeBetweenKeyword>true</PreferBreakBeforeBetweenKeyword>

  <PreferBreakBeforeConditionOperator>true</PreferBreakBeforeConditionOperator>

  <PreferBreakBeforeCteName>false</PreferBreakBeforeCteName>

  <PreferBreakBeforeEquals>true</PreferBreakBeforeEquals>

  <PreferBreakBeforeInsertTable>false</PreferBreakBeforeInsertTable>

  <PreferBreakBeforeThenKeyword>false</PreferBreakBeforeThenKeyword>

  <PreserveLineSpacingBetweenStatements>true</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>true</m_After>

    <m_Before>false</m_Before>

  </SpacesAroundFunctionCalls>

  <SpacesAroundInPredicateContents>

    <m_After>true</m_After>

    <m_Before>true</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>

  <SubsequentInsertColumnsListItemsBreakType>ChopAlways</SubsequentInsertColumnsListItemsBreakType>

  <SubsequentInsertValuesListItemsBreakType>SimpleWrap</SubsequentInsertValuesListItemsBreakType>

  <SubsequentListItemsBreakType>ChopAlways</SubsequentListItemsBreakType>

  <UseGlobalListOptionsForDmlStatements>false</UseGlobalListOptionsForDmlStatements>

  <UseObjectDefinitionCase>true</UseObjectDefinitionCase>

  <VerticallyAlignAliases>true</VerticallyAlignAliases>

  <VerticallyAlignClauseItems>true</VerticallyAlignClauseItems>

  <VerticallyAlignColumnDefinitions>false</VerticallyAlignColumnDefinitions>

  <VerticallyAlignComparisonOperators>false</VerticallyAlignComparisonOperators>

  <VerticallyAlignDataTypes>false</VerticallyAlignDataTypes>

  <VerticallyAlignJoinConditionWithJoinTable>false</VerticallyAlignJoinConditionWithJoinTable>

  <VerticallyAlignListItemComments>true</VerticallyAlignListItemComments>

  <VerticallyAlignListItems>true</VerticallyAlignListItems>

  <WhereClauseFirstItemBreakType>Never</WhereClauseFirstItemBreakType>

  <WrapColumn>120</WrapColumn>

  <WrapLongLines>true</WrapLongLines>

</LayoutOptions>

DSQU

I think I simply need the sql promp 10 JSON version of that configuration I posted in the previous post. 

DSQU

Hello - anyone that can help with this ? 

Kurt_M

Hi @DSQU

Apologies for the delay.

I went through the steps on my own system with your above script, and I was able to generate the new .JSON file. Please find the download link to that below:

https://files.red-gate.com/link/qTndZyLpIXjaNXztpQYV3P

Please place that in your styles folder and check that it works for you.

Thank you.

DSQU

Kurt_M said:

Hi @DSQU

Apologies for the delay.

I went through the steps on my own system with your above script, and I was able to generate the new .JSON file. Please find the download link to that below:

https://files.red-gate.com/link/qTndZyLpIXjaNXztpQYV3P

Please place that in your styles folder and check that it works for you.

Thank you.

Hi Kurt - thank you for sending that over. I seem to be still showing a difference between the 2 formats, all of our code compares are showing some spacing changes between the 2 format configurations but at this point we have to move forward so we will use this configuration which is close enough. Thanks again for your help.