« Sql toolbelt » : différence entre les versions
De Banane Atomic
Aller à la navigationAller à la recherche
(→Errors) |
|||
(13 versions intermédiaires par le même utilisateur non affichées) | |||
Ligne 185 : | Ligne 185 : | ||
& "C:\Program Files (x86)\Red Gate\SQL Compare 14\SQLCompare.exe" /S1:srv1 /db1:db1 /S2:srv2 /db2:db2 | & "C:\Program Files (x86)\Red Gate\SQL Compare 14\SQLCompare.exe" /S1:srv1 /db1:db1 /S2:srv2 /db2:db2 | ||
/include:table,view,storedprocedure,function,userdefinedtype | /include:table,view,storedprocedure,function,userdefinedtype | ||
/o:default,ip | /exclude:table:\[TableName\] | ||
/include:Identical # don't throw exception | /o:default,ip # ip = IgnorePermissions | ||
/sf:"C:\temp\out.sql" /force | /include:Identical # don't throw exception if the compared db are identical | ||
/verbose | /sf:"C:\temp\out.sql" /force # ScriptFile | ||
/sync # apply the changes on db2 | |||
/verbose | |||
</kode> | </kode> | ||
= SQL Data Compare = | = [https://documentation.red-gate.com/sdc/using-the-command-line SQL Data Compare] = | ||
Permet de comparer 2 bdd et de | Permet de comparer les données de 2 bdd et de les aligner. | ||
{{info | Si SQL Data Compare n'est pas accessible depuis le menu, SQL Search → icone blanche sur fond rouge → SQL Data Compare}} | {{info | Si SQL Data Compare n'est pas accessible depuis le menu, SQL Search → icone blanche sur fond rouge → SQL Data Compare}} | ||
<kode lang='ps'> | <kode lang='ps'> | ||
# compare the data and apply the changes | # compare the data and apply the changes | ||
"C:\Program Files (x86)\Red Gate\SQL Data Compare 14\SQLDataCompare.exe" /S1:server1 /db1:db1 /S2:server2 /db2:db2 /verbose | "C:\Program Files (x86)\Red Gate\SQL Data Compare 14\SQLDataCompare.exe" /S1:server1 /db1:db1 /S2:server2 /db2:db2 | ||
/include:Identical # don't throw exception if the compared db are identical | |||
/sync # apply the changes on db2 | |||
/verbose | |||
</kode> | </kode> | ||
= [https://documentation.red-gate.com/sca/getting-started/about-sql-change-automation/what-is-sql-change-automation SQL Change Automation] = | |||
Config file: | |||
<filebox fn='MyProject.sqlproj.user' lang=xml> | |||
<?xml version="1.0" encoding="utf-8"?> | |||
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> | |||
<PropertyGroup> | |||
<TargetDatabase>MyDb</TargetDatabase> | |||
<TargetConnectionString>Data Source=(local)\;Initial Catalog=MyDb;Integrated Security=True;Pooling=False;Encrypt=False;Trust Server Certificate=True;Multi Subnet Failover=False</TargetConnectionString> | |||
</PropertyGroup> | |||
</Project> | |||
</filebox> | |||
= SQL Source Control = | = SQL Source Control = | ||
Ligne 207 : | Ligne 224 : | ||
= Errors = | = Errors = | ||
== Database '..._SHADOW' does not exist. Make sure that the name is entered correctly == | |||
== Error Inserting Semicolons durant Format == | == Error Inserting Semicolons durant Format == | ||
SQL Prompt → Options → Format → Style → décocher Insert semicolons | SQL Prompt → Options → Format → Style → décocher Insert semicolons | ||
Ligne 219 : | Ligne 238 : | ||
# activer la licence | # activer la licence | ||
& 'C:\Program Files (x86)\Red Gate\SQL Compare 13\SQLCompare.exe' /activateSerial:001-123-456789-ABCD | & 'C:\Program Files (x86)\Red Gate\SQL Compare 13\SQLCompare.exe' /activateSerial:001-123-456789-ABCD | ||
</kode> | |||
= [https://chocolatey.org/packages/SqlToolbelt Installation with chocolatey] = | |||
<kode lang='ps'> | |||
# install only sone products | |||
chocolatey install sqltoolbelt --params "/products:'SQL Compare, SQL Data Compare, SQL Prompt, SQL Doc, SQL Test, SSMS Integration Pack, SQL Change Automation'" | |||
</kode> | </kode> |
Dernière version du 11 octobre 2022 à 10:30
SQL Toolbelt
Groupement de produits Redgate pour le dévelopement et le déploiement sur SQL Server.
SQL Prompt
Formater le code:
- Ctrl + K, Y
- SQL Prompt → Format SQL
CustomFormatStyle.sqlpromptstylev2 |
<?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>true</CollapseCaseExpressionIfShort> <CollapseCaseIfShortCharacterCount>160</CollapseCaseIfShortCharacterCount> <CollapseIfShortControlFlowCharacterCount>78</CollapseIfShortControlFlowCharacterCount> <CollapseIfShortDdlCharacterCount>75</CollapseIfShortDdlCharacterCount> <CollapseIfShortDmlCharacterCount>120</CollapseIfShortDmlCharacterCount> <CollapseIfShortParenthesesContentsCharacterCount>160</CollapseIfShortParenthesesContentsCharacterCount> <CollapseIfShortSubqueryCharacterCount>120</CollapseIfShortSubqueryCharacterCount> <CollapseShortControlFlowStatements>false</CollapseShortControlFlowStatements> <CollapseShortDdlStatements>true</CollapseShortDdlStatements> <CollapseShortDmlStatements>true</CollapseShortDmlStatements> <CollapseShortParenthesesContents>true</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>ExpandedToStatement</CteContentsParenthesisLayout> <CteContentsParenthesisedExpressionAlignment>ToStartOfOpeningBracket</CteContentsParenthesisedExpressionAlignment> <CteContentsParenthesisedExpressionBreakType>Never</CteContentsParenthesisedExpressionBreakType> <FirstCreateAlterDefintionBreakType>ChopIfLongOrMultipleItems</FirstCreateAlterDefintionBreakType> <FirstCreateProcedureParamBreakType>ChopIfLongOrMultipleItems</FirstCreateProcedureParamBreakType> <FirstListItemBreakType>Never</FirstListItemBreakType> <FromClauseFirstItemBreakType>Never</FromClauseFirstItemBreakType> <FunctionArgumentsBreakType>ChopAlways</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>true</IndentCteContents> <IndentCteName>false</IndentCteName> <IndentDdlClauses>false</IndentDdlClauses> <IndentInsertColumnListsParenthesisedExpression>true</IndentInsertColumnListsParenthesisedExpression> <IndentInsertValuesListsParenthesisedExpression>true</IndentInsertValuesListsParenthesisedExpression> <IndentJoinTable>false</IndentJoinTable> <IndentListItems>true</IndentListItems> <IndentParenthesisedExpression>false</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>Pricing</Name> <NewLineAfterTopRowFilter>true</NewLineAfterTopRowFilter> <NewLineBeforeTopRowFilter>false</NewLineBeforeTopRowFilter> <NewLineForCorrelatedTableSource>true</NewLineForCorrelatedTableSource> <NumberOfEmptyLinesAfterBatchSeparator>1</NumberOfEmptyLinesAfterBatchSeparator> <NumberOfEmptyLinesBetweenStatements>0</NumberOfEmptyLinesBetweenStatements> <NumberOfSpacesInTab>4</NumberOfSpacesInTab> <OpeningParenthesisAlignment>ToStatement</OpeningParenthesisAlignment> <OpeningParenthesisBreakType>ChopAlways</OpeningParenthesisBreakType> <OptionsVersion>12</OptionsVersion> <OverrideParenthesesOptionsForCreateAlterStatements>true</OverrideParenthesesOptionsForCreateAlterStatements> <ParenthesisLayout>CompactSimple</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>false</PlaceCommasBeforeListItems> <PlaceJoinOnKeywordOnANewLine>true</PlaceJoinOnKeywordOnANewLine> <PlaceJoinTableOnNewLine>false</PlaceJoinTableOnNewLine> <PreferBreakBeforeAsKeyword>false</PreferBreakBeforeAsKeyword> <PreferBreakBeforeBetweenAndKeyword>false</PreferBreakBeforeBetweenAndKeyword> <PreferBreakBeforeBetweenKeyword>true</PreferBreakBeforeBetweenKeyword> <PreferBreakBeforeConditionOperator>true</PreferBreakBeforeConditionOperator> <PreferBreakBeforeCteName>true</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>true</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>false</VerticallyAlignAliases> <VerticallyAlignClauseItems>false</VerticallyAlignClauseItems> <VerticallyAlignColumnDefinitions>false</VerticallyAlignColumnDefinitions> <VerticallyAlignComparisonOperators>false</VerticallyAlignComparisonOperators> <VerticallyAlignDataTypes>false</VerticallyAlignDataTypes> <VerticallyAlignJoinConditionWithJoinTable>false</VerticallyAlignJoinConditionWithJoinTable> <VerticallyAlignListItemComments>true</VerticallyAlignListItemComments> <VerticallyAlignListItems>true</VerticallyAlignListItems> <WhereClauseFirstItemBreakType>Never</WhereClauseFirstItemBreakType> <WrapColumn>160</WrapColumn> <WrapLongLines>true</WrapLongLines> </LayoutOptions> |
Restore pinned tabs
SQL Prompt restore les onglets précédement ouvert mais le status pinned est perdu.
SQL Compare
& "C:\Program Files (x86)\Red Gate\SQL Compare 14\SQLCompare.exe" /S1:srv1 /db1:db1 /S2:srv2 /db2:db2 /include:table,view,storedprocedure,function,userdefinedtype /exclude:table:\[TableName\] /o:default,ip # ip = IgnorePermissions /include:Identical # don't throw exception if the compared db are identical /sf:"C:\temp\out.sql" /force # ScriptFile /sync # apply the changes on db2 /verbose |
SQL Data Compare
Permet de comparer les données de 2 bdd et de les aligner.
Si SQL Data Compare n'est pas accessible depuis le menu, SQL Search → icone blanche sur fond rouge → SQL Data Compare |
# compare the data and apply the changes "C:\Program Files (x86)\Red Gate\SQL Data Compare 14\SQLDataCompare.exe" /S1:server1 /db1:db1 /S2:server2 /db2:db2 /include:Identical # don't throw exception if the compared db are identical /sync # apply the changes on db2 /verbose |
SQL Change Automation
Config file:
MyProject.sqlproj.user |
<?xml version="1.0" encoding="utf-8"?> <Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <PropertyGroup> <TargetDatabase>MyDb</TargetDatabase> <TargetConnectionString>Data Source=(local)\;Initial Catalog=MyDb;Integrated Security=True;Pooling=False;Encrypt=False;Trust Server Certificate=True;Multi Subnet Failover=False</TargetConnectionString> </PropertyGroup> </Project> |
SQL Source Control
Migration scripts
Création d'un script de migration de données.
La table RedGateLocal.DeploymentMetadata contient la liste des scripts de migration qui ont été appliqué.
Errors
Database '..._SHADOW' does not exist. Make sure that the name is entered correctly
Error Inserting Semicolons durant Format
SQL Prompt → Options → Format → Style → décocher Insert semicolons
The 'SqlSourceControlPackage' package did not load correctly
Licence
# désactiver la licence & 'C:\Program Files (x86)\Red Gate\SQL Compare 13\SQLCompare.exe' /deactivateSerial # activer la licence & 'C:\Program Files (x86)\Red Gate\SQL Compare 13\SQLCompare.exe' /activateSerial:001-123-456789-ABCD |
Installation with chocolatey
# install only sone products chocolatey install sqltoolbelt --params "/products:'SQL Compare, SQL Data Compare, SQL Prompt, SQL Doc, SQL Test, SSMS Integration Pack, SQL Change Automation'" |