4

I am currently working on a project to publish our database as a data tier application. The database is fairly complex and has required several parameters to be modified when either using SQLPackage.exe or publishing via Visual Studio.

One of the scenarios that is currently preventing me from setting my item to done is:

We have a non-nullable column being added to a table, we apply "SmartDefaults, when applicable" as true when publishing the dacpac to allow the dacpac to complete, this is working great.

What isn't working is:

When using the Upgrade Data-tier Application in SQL Management Studio. It doesn't allow me to specify the same option, which prevents the database from being upgraded correctly.

Has anyone been able to do this?

Slavvy
  • 551
  • 7
  • 13
  • What version of SQL Server Management Studio are you using? I just tried SSMS 2012 and you're right, the option isn't there. I'll see if I can install the 2014 client and see if they've added the option. – JamesQMurphy Mar 13 '15 at 18:29
  • Hi James, SSMS 2012 is the version I am using. From what I've read so far about 2014, I don't believe it will be included. I find it quite strange and it makes me think they will eventually bin SSMS if they aren't keeping up-to-date with these features. – Slavvy Mar 18 '15 at 09:34

2 Answers2

3

You can add a paramater to the DACPAC /p:GenerateSmartDefaults=true if you're using a CI/CD tool like Azure DevOps

Azure Dev Ops Release - SQL Databse Deployment

If you're doing a right click publish database, then you can select the option under "Generate Script"

Select: Generate Script Option

Select: Generate Smart Default, when applicable

Dwain Browne
  • 792
  • 7
  • 12
-4

Please visit these sites perhaps can help you:

  1. http://www.incyclesoftware.com/2012/11/better-way-to-add-not-null-column-to-an-existing-table-and-preserve-data-using-ssdt/
  2. https://social.msdn.microsoft.com/forums/sqlserver/en-US/05e195cb-2005-4dcf-84db-705b22972dc8/prepredeployment-script-in-database-projects
Mahmoud Bayoush
  • 61
  • 5
  • 4
    The information at these links may very well be helpful, but we frown on link-only answers here. Once the links break (and they _will_ break), your answer will have no value. Please summarize or include some of the information from those links in your answer. – John Saunders Dec 13 '14 at 21:50
  • Unfortunately not, the options referred in these articles are the "Generate Smart Defaults" you can either specify by a Visual Studio publish or a SqlCmd publish. I need to be able to do it by SQL Management Studio but I've found a way yet. Thanks for your time. – Slavvy Dec 15 '14 at 09:39