4

I have a SQL Server database project in Visual Studio and I have dropped an existing stored procedure. I also have a database locally where that exact stored procedure exists.

When I run a schema compare from within Visual Studio on both, in the resulting schema compare window, I can see the stored procedure that will be dropped in the generated deployment script. However, when I invoke sqlPackage.exe from a Powershell script, the generated deployment script does not contain a drop statement for the stored procedure. Interestingly, when I make a change to the stored procedure, then it is included in the generated deployment script with an alter statement.

Why is sqlPackage.exe omitting the drop stored procedure statement when I remove it but including it for a change? I feel it's a parameter setting I need to feed to sqlPackage.exe but they seem to be opt out, not opt in (https://msdn.microsoft.com/library/hh550080(vs.103).aspx#Anchor_7).

Below is my command from Powershell:

& 'sqlPackage' '/Action:Script' "/SourceFile:$sourceDacpacFile" "/TargetFile:$targetDacpacFile" "/OutputPath:$outputPath" "/TargetDatabaseName:$targetDatabaseName" "/p:AllowIncompatiblePlatform=$allowIncompatiblePlatform" "/p:BlockOnPossibleDataLoss=$blockOnPossibleDataLoss" "/p:DropIndexesNotInSource=$dropIndexesNotInSource"
dhughes
  • 645
  • 1
  • 7
  • 19

1 Answers1

8

Take a look at the property DropObjectsNotInSource

Specifies whether objects that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publishto a database. This value takes precedence over DropExtendedProperties.

The default is False see: DacDeployOptions.DropObjectsNotInSource Property

David Martin
  • 11,764
  • 1
  • 61
  • 74
  • sir. is there any simple article discussing the benefits of sqlpackage.exe except MSDN articles? many thanks in advance. – ahmed abdelqader Dec 07 '16 at 11:07
  • 1
    There are lots of articles about SqlPackage, try searching for SSDT or Data Dude - google is your friend. The benefits are subjective, it depends on what is important to you. Personally I use SSDT on what I consider to be large a database deployment (~100 solutions, ~1000 projects of which ~50 are database projects) and I am very happy with how it works for me. Do your research before selecting this, if it works for you then go for it. – David Martin Dec 07 '16 at 11:20
  • I am using SSDT database project and its working fine updating the schema of my table but its not dropping tables/SPs on production Azure sql server, although i have CHECKED the database solution properties "Drop object in target but not in solution" but no respect. – Saad Awan Mar 27 '20 at 05:48