0

I'm using SQLPackage in TFS to automate an SSDT project/DACPAC deployment with SQL Server 2014. I have a table-valued function that appears in the deployment report and script with every deployment, even though the source code and compiled rarely change. Example; I can do a new build and deployment with no source code changes, and my deployment report will look like this (the corresponding SQL script will have the definition matching what's already in the DB):

<Operations>
  <Operation Name="Alter">
    <Item Type="SqlMultiStatementTableValuedFunction" Value="[stuff].[MyTVF]">
  </Operation>
</Operations>

I would expect the deployment to have nothing in it. It's the only object for which this happens, in a project containing thousands of objects. Anybody had an experience like this with SQLPackage/TFS/DACPAC before?

1
  • If you did a schema compare from the Visual Studio project to the target database, does the function show up there? Apr 30 '21 at 1:44
0

My suggestion is to review the ignore list in the publish options. Things like case sensitive/insensitive or columns order in tables can generate this kind of thiing.

Verify with visual studio compare (if it is possible) with the same publishing options what is happening to highlight codes differences.

What happens if you deploy your dacpac a second time? The TVF alter should disappear. And after 24 hours? If it returned that means that an application or a script have altered or dropped an recreated it with maybe a simple space or carriage return as a difference and then your deploy is going to find again that an alter is needed.

If you can use visual studio for compare, eventually try to compare with notepad++ getting the alter script from the instance and the alter script from the dacpac apply script generated with the sqlpackage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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