7

I am trying to deploy an ssdt project using sqlpackage.exe utility. I get the following error (in German):

Fehler bei der Erstellung des Bereitstellungsplans. Die Bereitstellung kann nicht fortgesetzt werden. Die Property-Klasse Length ist nicht in der Element- oder Annotation-Klasse PersistedResolvableAnnotation enthalten.

It means sth. like:

Error while creating deployment plan. Deployment cannot proceed. The property-class Length is not included in the element or annotation class PersistedResolvableAnnotation.

I could not find much information about "PersistedResolvableAnnotation". But I realized that it is included in the model.xml in the dacpac.

It contains handful of SqlLogins defined similar to this one:

<Element Type="SqlUser" Name="[Angela]">
            <Property Name="IsWithoutLogin" Value="True" />
            <Relationship Name="DefaultSchema">
                <Entry>
                    <References Name="[Angela]" Disambiguator="8" />
                    <Annotation Type="**PersistedResolvableAnnotation**" Name="[Angela]">
                        <Property Name="TargetTypeStorage" Value="SqlSchema" />
                        <Property Name="Length" Value="8" />
                        <Property Name="Offset" Value="62" />
                    </Annotation>
                </Entry>
            </Relationship>
        </Element>

Angela (and the other candidates) do not exist on destination server as Login, although they are registered database users in the target db. I would expect another error - if any - than this useless one.

The database the project is based on a pretty old SQL Server verison. Is it possible that this is some deprecated feature / syntax or property coming from Sql Server version and is simply not supported ? Does anyone know more details?

Magier
  • 4,585
  • 6
  • 37
  • 75

3 Answers3

7

I found the issue myself. I will document it in case anyone else is facing the issue in future.

The following user definition:

CREATE USER [Angela] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [Angela];

created the following code in the model.xml that avoids publishing, maybe because the schema (Angela) used there is invalid because it simply does not exist:

<Element Type="SqlUser" Name="[Angela]">
            <Property Name="IsWithoutLogin" Value="True" />
            <Relationship Name="DefaultSchema">
                <Entry>
                    <Annotation Type="PersistedResolvableAnnotation" Name="[Angela]">
                        <Property Name="TargetTypeStorage" Value="SqlSchema" />
                        <Property Name="Length" Value="9" />
                        <Property Name="Offset" Value="63" />
                    </Annotation>
                </Entry>
            </Relationship>
        </Element>

After changing the user definition to:

CREATE USER [Angela] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [dbo];

the model lookes as followes and could be published:

<Element Type="SqlUser" Name="[Angela]">
        <Property Name="IsWithoutLogin" Value="True" />
    </Element>

I have a feeling that this strange behavior is kind of a follow-up of another misbehavior because I would expect the not existing schema to raise a compiler error.

Magier
  • 4,585
  • 6
  • 37
  • 75
2

For me, the solution was that I was using an outdated version of SqlPackage.exe.

Old version path:

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe

New version path:

C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe

It is installed by Microsoft SQL Server Data Tools (SSDT).

umbyersw
  • 121
  • 2
1

For us, this was caused by installing Visual Studio 2015 on our build servers.

I followed the steps in one of the answers in this link

Thanks for reaching out regarding this. From time to time it's necessary for us to change the format of the model.xml file, and when this happens it's necessary to install the updated version of SqlPackage.exe, so that it can read the revised format. The download is available here: https://www.microsoft.com/en-us/download/details.aspx?id=51238

as well as making use of a new version of SqlPackage at

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\sqlpackage.exe

nebffa
  • 111
  • 3