search

microsoft / DacFx

DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
https://aka.ms/sqlpackage-ref
MIT License
356 stars 20 forks source link

Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: The Element or Annotation class SqlIndex does not contain the Property class Online #427

Closed jackliusr closed 8 months ago

jackliusr commented 8 months ago

Steps to Reproduce:

  1. import a dacpac

(DacFx/SqlPackage/SSMS/Azure Data Studio)

dzsquared commented 8 months ago

Can you share a bit more about how the dacpac was created?

jackliusr commented 8 months ago

I run below command to build and get the dacpac file.

dotnet build ddd.sqlproj

ddd.sqlproj

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" ToolsVersion="4.0">
  <Sdk Name="Microsoft.Build.Sql" Version="0.1.15-preview" />
ErikEJ commented 8 months ago

Please double check your sqlpackage version

jackliusr commented 8 months ago

Below is the version info, command I used to get, and location of the sqlpackage.

jack@DIM-I-1HVZJR3:~$ sqlpackage /version
162.2.111.2
jack@DIM-I-1HVZJR3:~$ sqlpackage /version
162.2.111.2
jack@DIM-I-1HVZJR3:~$ which sqlpackage
/home/jack/.dotnet/tools/sqlpackage
jack@DIM-I-1HVZJR3:~$
PaulVrugt commented 8 months ago

Running into the same issue. There is very little logging about what's actually going wrong

It's happening since updating visual studio to the latest version on our azure pipelines agent image. All our database builds are now failing

Thomas386 commented 8 months ago

Same issue: In the generated .dacpac file we now have a model.xml having with a property 'online' which causes the error message. Sample:

<Element Type="SqlIndex" Name="[dbo].[Core_Contact].[IX_Core_Contact]">
    <Property Name="Online" Value="False" />
    <Relationship Name="ColumnSpecifications">
        <Entry>
            <Element Type="SqlIndexedColumnSpecification">
                <Relationship Name="Column">
                    <Entry>
                        <References Name="[dbo].[Core_Contact].[Id]" />
                    </Entry>
                </Relationship>
            </Element>
        </Entry>
        <Entry> etc.

If this line with the 'Online' property does not exist: No problem. `

Using VS 17.9.3: This line exists. Using VS 17.9.2: This line does not exist.

The line is written, if you define the index with the option 'ONLINE'. Sample:

CREATE NONCLUSTERED INDEX [IX_Core_Contact] ON [dbo].[Core_Contact]
(
    [Id] ASC,
    [Mandator_Id] ASC
)
INCLUDE ( [ShortName]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
PaulVrugt commented 8 months ago

@Thomas386 great work sherlock. This will probably solve the issue for us. We have exactly 1 index in the project with the online property set

nevertheless, this should be fixed

llali commented 8 months ago

@Thomas386 is the version of sqlpackage from AzurePipelin agent? This issue only happens if the version sqlpackage used to deploy is older than the version of dacfx used in SQLProject. can you confirm that you get the bug if you try to deploy locally ( not from Azure Pipeline) using sqlpcakge 162.2.111.2? Azure pipeline agents are usually have delay on updating to latest version of sqlpcakge.

PaulVrugt commented 8 months ago

Isn't sql package update included in the visual studio updates? We started seeing the issue after updating visual studio on our own custom image for azure pipelines. Wouldn't this update both sqlproject and sqlpackage?

jgillette commented 8 months ago

We had the same issue crop up today after we patched our build server's Visual Studio to the latest version. Updating our deployer to use the latest 162.2.111 SqlPackage fixed the issue.

llali commented 8 months ago

@PaulVrugt when using Azure pipeline for deploy, the sqlpckage is coming from the agent not from Visual Studio and usually there's a delay between when we ship a new sqlpckage and when the agents get the update. VS also uses DacFx to build the project so in your case, you used DacFx 162.2 to build the project, because you updated VS but to deploy you are using 162.1 version of sqlpackge which is coming from Azure pipeline agent.

JoshuaMillerNebraskablue commented 8 months ago

We had the same issue crop up today after we patched our build server's Visual Studio to the latest version. Updating our deployer to use the latest 162.2.111 SqlPackage fixed the issue.

@jgillette - When you say upgraded the deployer, are you referring to the Azure DevOps build agent itself to the latest version?

llali commented 8 months ago

if using azure pipeline and not using self-hosted agent, you can check the version of image picked by you pipeline and see if it matches the latest image here https://github.com/actions/runner-images?tab=readme-ov-file#faqs

PaulVrugt commented 8 months ago

@PaulVrugt when using Azure pipeline for deploy, the sqlpckage is coming from the agent not from Visual Studio and usually there's a delay between when we ship a new sqlpckage and when the agents get the update. VS also uses DacFx to build the project so in your case, you used DacFx 162.2 to build the project, because you updated VS but to deploy you are using 162.1 version of sqlpackge which is coming from Azure pipeline agent.

But we are using a private agent with the latest visual studio installed. We are not using hosted agents. There is no delay because there is only 1 thing (the agent image) to update. Apparently the latest vs update does not include an update of sqlpackage

llali commented 8 months ago

@PaulVrugt are you using sqlpackage from VS location? would you please send the path? it's possible that you need to update sqlpackage using https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16 in your agent

Thomas386 commented 8 months ago

@llali : Thx for your help. -> We are on old versions and we will fix this now.

PaulVrugt commented 8 months ago

We are using the SqlAzureDacpacDeployment@1 in azure pipelines, which apparently uses the standalone version of sqlpackage in C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe and not the vs included one in: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe

this is where the issue occurs. It would have been a bit better if the vs included one was used. Anyway, we know the cause now

b3go commented 8 months ago

We are using the SqlAzureDacpacDeployment@1 in azure pipelines, which apparently uses the standalone version of sqlpackage in C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe and not the vs included one in: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe

this is where the issue occurs. It would have been a bit better if the vs included one was used. Anyway, we know the cause now

The reason why the step isn't able to pick up the sqlpackage.exe in the Visual Studio installation folder is probably located here: https://github.com/microsoft/azure-pipelines-extensions/blob/ae1eb2b556298beb949fc61e885e55fc18abed07/TaskModules/powershell/TaskModuleSqlUtility/SqlPackageOnTargetMachines.ps1#L327

Debugging this showed it is looking for the path: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe\SqlPackage.exe

I guess the folder structure has changed in any of the visual studio versions.

PaulVrugt commented 8 months ago

We are using the SqlAzureDacpacDeployment@1 in azure pipelines, which apparently uses the standalone version of sqlpackage in C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe and not the vs included one in: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe this is where the issue occurs. It would have been a bit better if the vs included one was used. Anyway, we know the cause now

The reason why the step isn't able to pick up the sqlpackage.exe in the Visual Studio installation folder is probably located here: https://github.com/microsoft/azure-pipelines-extensions/blob/ae1eb2b556298beb949fc61e885e55fc18abed07/TaskModules/powershell/TaskModuleSqlUtility/SqlPackageOnTargetMachines.ps1#L327

Debugging this showed it is looking for the path: C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\SqlPackage.exe\SqlPackage.exe

I guess the folder structure has changed in any of the visual studio versions.

haha that simply looks like a bug. It's using the SqlPackage.exe double

ErikEJ commented 8 months ago

I think the debugging conclusion is wrong.

b3go commented 8 months ago

Well the part I posted is iterating through all elements in the DAC folder and appending sqlpackage.exe. There are no subfolders in the DAC folder for VS 2022. Which results in test-path calls for paths like the one I mentioned.

ErikEJ commented 8 months ago

@b3go I see! It used to be in a number folder under the VS folder...

ErikEJ commented 8 months ago

@dzsquared FYI! Do you take a PR to fix this?

tpressleyhlr commented 8 months ago

We encountered this issue today with the latest version of the windows-latest VM. Updating our local release agents to use the newest version of sqlpackage resolved the issue.

bheemvennapureddy commented 8 months ago

Pulling latest SQL package fixed this for us. Thanks

dieterQBIT commented 8 months ago

That doens't seem to work for me. I updated to visual studio 17.9.4 and SqlPackage to 162.2.111 from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16, but still getting the same error

ErikEJ commented 8 months ago

@dieterQBIT In which context are you geeting the error?

ErikEJ commented 8 months ago

@dieterQBIT That does not look like the same error ?? More like file not found

dieterQBIT commented 8 months ago

sorry, project wasn't properly built. This is the error I get when trying to deploy image

dieterQBIT commented 8 months ago

I just noticed those older versions are also still installed. Should I remove them? image

ErikEJ commented 8 months ago

@dieterQBIT Please share your deployment script/batch file, screenshots are not so helpful

dieterQBIT commented 8 months ago

EasyOffice.deploy_allowdataloss.zip

This worked perfectly until I updated to visual studio 17.9.3

b3go commented 8 months ago

That doens't seem to work for me. I updated to visual studio 17.9.4 and SqlPackage to 162.2.111 from https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16, but still getting the same error

Did you install using dotnet tool install? These are also not getting picked up by the PowerShell. You have to install using the DacFramework.msi

dieterQBIT commented 8 months ago

I used 'Download and run the DacFramework.msi installer for Windows' under Windows (.NET Framework)

b3go commented 8 months ago

Can you run the pipeline with diagnostics? It will show you which sqlpackage.exe is used.

dieterQBIT commented 8 months ago

It shows Running 64-bit SqlPackage Version 162.0.52.1 on .NET Core So where does it come from?

ErikEJ commented 8 months ago

@dieterQBIT .NET global tool?

Try to update with:

dotnet tool update Microsoft.Sqlpackage -g

dieterQBIT commented 8 months ago

This works for me! Thanks a lot!

But how is this possible? I never used .NET Core before.

ErikEJ commented 8 months ago

Someon or some app installed it

dieterQBIT commented 8 months ago

strange...but again, thanks! case closed for me :-)

williambuchanan2 commented 8 months ago

Just started happening to us as well last night. For us it is happening in Azure Devops pipelines when deploying DB updates.

Not happening in Visual Studio on the latest version 17.9.4

ErikEJ commented 8 months ago

@williambuchanan2 own agent or Microsoft hosted?

Can you share your task ?

williambuchanan2 commented 8 months ago

@williambuchanan2 own agent or Microsoft hosted?

Can you share your task ?

Microsoft hosted. Had to remove all the "ONLINE =..." from our DB project to get it to deploy.

ErikEJ commented 8 months ago

@williambuchanan2 can you share the task yaml snippet?

williambuchanan2 commented 8 months ago

@williambuchanan2 can you share the task yaml snippet?

We aren't using yaml as such. We are using the "SQL Server database deploy" task, but here is the yaml from that:

variables: PDSDatabaseDacpac: '$(System.DefaultWorkingDirectory)/_Database ($(Release.EnvironmentName))/drop/Database/PDS.Database/bin/Release/PDS.Database.dacpac' DBServer: 'XXX' DBUsername: 'xxxx' DBPassword: 'xxxx' Args_Mining: '/v:xxx=xxxx /v:CustomerType=Mining /v:LegacyCardinalityEstimation=OFF'

steps:

ptoutant commented 8 months ago

Having same exact error. I'm on VS 2022 17.9.4. If I build with VS 2019 16.11.34, works fine.

llali commented 8 months ago

closing this since it's not a dacfx bug

dzsquared commented 8 months ago

Resolution You may need to update SqlPackage on your pipeline agent - https://learn.microsoft.com/sql/tools/sqlpackage/sqlpackage-download

Other notes It looks like one of the ADO tasks is also not pulling SqlPackage from the correct location. That repo is open to PRs, please feel free to tag me if you're able to jump in to correct before I get around to it. (https://github.com/microsoft/DacFx/issues/427#issuecomment-2004423545)

ErikEJ commented 8 months ago

@b3go Interested in doing a PR to fix the AzDo task bug?

ErikEJ commented 8 months ago

@dzsquared Happy to do a PR, but what version number should be returned for the dacFX under VS now that there is no folder with a version number? 999? Or open a .dll file in the folder an extract the number?

See the Get-SqlPackageOnTargetMachine method in https://github.com/microsoft/azure-pipelines-extensions/blob/master/TaskModules/powershell/TaskModuleSqlUtility/SqlPackageOnTargetMachines.ps1