In the project settings of a database project I can set the version number form my data-tier application, however setting this manually is a bit inefficient. I would like to automatically increment the build number, as with other VS projects. How can this be done ? At the same time I would like to see this version number in the generated dacpac file.
Sql-server – Automatic Version increment on Data-Tier Applications
data-tier-applicationsql serversql-server-2012ssdt
Related Solutions
I had this error recently so I'll add my solution for anyone else who comes across this,
Turns out in my publish.xml I had RegisterDataTierApplication set to True. The first time I published the database it worked fine, but then I got the same error, as the database was already registered as a Data Tier application.
By setting to false (or unchecking the checkbox in the gui) it works fine.
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<IncludeCompositeObjects>True</IncludeCompositeObjects>
<TargetDatabaseName>foo</TargetDatabaseName>
<DeployScriptFileName>foo.sql</DeployScriptFileName>
<BlockWhenDriftDetected>False</BlockWhenDriftDetected>
<RegisterDataTierApplication>False</RegisterDataTierApplication>
<ProfileVersionNumber>1</ProfileVersionNumber>
<TargetConnectionString>Data Source=foo;Integrated Security=True;Pooling=False</TargetConnectionString>
</PropertyGroup>
</Project>
As a note about extracting the dacpac - you will always get an error if extracting via ssms,
If you use the SQL Server Object Explorer in VisualStudio, you can set more options, including unchecking the 'Verify Extraction' option, which will then allow you to create the dacpac.
There is no direct way to set/change the ownership as a part of CREATE or ALTER DATABASE statement.
If you want to enforce that, you can do this by adding a post deployment script which calls sp_changedbowner.
This article describes that in detail. Similar approach can be applied to set the initial file size and growth Rate as well.
Also, as a note: sp_changedbowner is now depreciated, so it is better to use ALTER AUTHORIZATION
Related Question
- Sql-server – Howto create useful dacpac versioning along with SSDT deployment
- Sql-server – SQLPackage.exe not picking up changes
- Sql-server – DACPAC and database drift: db users are always detected as drift even when nothing has changed
- Sql-server – way to easily validate that a SQL Script is compatible with a specific SQL Server version
- Sql-server – Error creating memory optimized filegroup in SSDT
- Sql-server – Excluding database objects from a DacPac database deployment
Best Answer
I found this forum thread had an answer that worked well: Automatic Version increment on Data-Tier Applications
Minimum steps are:
Create
Properties\AssemblyInfo.csif it doesn't already exist, and make sure it's included in the project and contains at least this code:using System.Reflection; [assembly: AssemblyVersion("1.0.*")]Or however you want your version number to go.Edit the
*.sqlprojfile in an outside editor (notepad etc) and add the following XML: