1

first I'm sory for my broken English.

I have two different databases in two different SQL instances, and both instances have references to each other by linked server. I have added this references in the database solution and use a variable to invoke the link. This is working ok and building fine.

I have a problem when automating database deployment using sqlpackage. After each internal release DACPAC file is generated. On the next sprint, when a deployment is required, a comparison is made using SQLPackage.exe. This generates a script with the new objects generated in the last sprint and is working OK except for one thing.

When I make the comparison between the latest .DACPAC and the .DACPAC built in the last release every object referring to the linked server appears in the resulting script file. This isn't a big problem per-se but when delivering to the client there are objects that didn't need to change.

Is there an option that specifies includes of linked server variables? Is there a way to select which object to include (i'm afraid of this one because there could be a scenario when this object is modified and should be included in the package)?

EDIT: This object appear only when using sqlpackage. They don't appear in visual studio 2012 schema compare.

This are the parameters I'm using now: 

SqlPackage.exe /Action:script /p:BlockOnPossibleDataLoss=False /OverwriteFiles:True "/OutputPath:delta_scripts.publish.sql" /p:CommentOutSetVarDeclarations=True /p:DropPermissionsNotInSource=False /p:DropRoleMembersNotInSource=False /p:GenerateSmartDefaults=True /p:IgnorePermissions=True /p:IgnoreRoleMembership=True /p:IgnoreUserSettingsObjects=True /p:IgnoreCryptographicProviderFilePath=True /p:IgnoreLoginSids=True /p:IgnoreIdentitySeed=True /p:IgnoreFilegroupPlacement=False /p:IgnoreExtendedProperties=True /p:IncludeTransactionalScripts=False /p:ScriptDeployStateChecks=False /p:ScriptDatabaseOptions=False /v:Path1=C: /v:Path2=C: /v:Path3=C: /v:Path4=C: /v:Path5=C: /v:Path6=C: /v:Path7=C: /v:Path8=C: /v:Path9=C: "/TargetFile:olddacpac.dacpac" "/SourceFile:newdacpac.dacpac" "/TargetDatabaseName:DBNAME" "/SourceDatabaseName:DBNAME" /Variables:linkedserver=linkedserver /Variables:tempdb=tempdb /Variables:master=master /Variables:msdb=msdb
R G
  • 21
  • 4
  • Don't think it's possible directly, but there is support in the latest version of a sort: http://blogs.msdn.com/b/ssdt/archive/2014/07/15/msbuild-support-for-schema-compare-is-available.aspx – Peter Schott Aug 29 '14 at 15:00
  • Thanks I will try this today. I think this will work in an autodployment setting (our local environments) but it won't work on client package delivery because it doesn't support Generate DB Update script which is the thing that has me more worried. – R G Sep 01 '14 at 14:56
  • Confirming that this problem still occurs with VS 2017 and most recent version of SQLPackage. Scripting of historic DACPAC to new DACPAC produces changed objects, even when those objects have not changed. – CrazyIvan1974 Mar 18 '19 at 15:43

0 Answers0