4

I am having problems publishing a SSDT database project and registering it as a data tier application. Let me explain.

I have a database (A) which references two other databases (B & C) through linked servers. I have created projects based on B and C and snapshoted the projects to create dacpac's for databases B and C. I have created a database project for database A which has database references to B and C through dacpac's. I have set SQLCMD variables and modified the db project ddl scripts to use the SQLCMD variables in place of the un-resolved linked server names. The project builds!

I am trying to publish the project as a data tier application but keep receiving the following error "Databases registered as a DAC database must be hosted by an instance of SQL 2005 SP4, SQL 2008 SP2, SQL 2008 R2, SQL 2012 or SQL Azure". Incidentally I am running SQL server 2012.

I thought I would test whether I could register as a data tier application through SSMS. Within SSMS the option to "Register as data Tier Application" is grayed out. I therefore tried to "Export Data Tier Application" and received a number of error in reference to the linked server objects.

My question is; is it possible to deploy a SSDT database project and register it as a Data Tier Application where the project is using linked servers, or am I doing something wrong? If it is possible could some one provide some advice.

I have broken Google looking for the answer, so any help would be greatly appreciated. I have previously asked this question on StackOverflow but got no response so I am posting here in a more targeted forum.

Improve this question
3
  • Try adding a database reference to the linked server .dacpacs instead of creating SQLCMD references manually (screenshot here).
    – Mark Storey-Smith
    Commented Apr 25, 2013 at 11:46
  • Thanks Mark, I must not have made my self very clear, but your screenshot certainly did. I have added a database reference to the database which the linked server is enabling me to communicate with, I have done this by refereeing a dacpac through the wizard in your screenshot. The SQLCMDs i refer to are where a variable is defined in the wizard which is used through the project to reference the dacpac (linked server object). Hope that's clearer, but thank you for your help.
    – Steve
    Commented May 2, 2013 at 10:23
  • I wonder if you ever were able to deploy a tiny sample database project to the used target server without any database references just to make sure this worked. Guess it's way too late to test this but that is what I would evaluate first. I don't really think the references are involved to this error.
    – Magier
    Commented Apr 7, 2016 at 9:17

1 Answer 1

Reset to default
3

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>

Publish Database Profile

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.