Errors Occurred While Modeling the Target Database Deployment Can Not Continue

Today I'd like to talk about three error messages you might see when deploying an SSDT database project, either through Visual Studio or via a dacpac and script. I'm going to focus here on what you see from inside of Visual Studio, but you will see similar errors returned when you deploy using a script and the reasons behind them will be the same.

Chances are whatever the error is, you will start off with something like:

This appears in the Data Tools Operations tab, usually at the bottom of the Visual Studio window

From here, you need to click on the View Results link inside Visual Studio, which brings up a tab with the release script in the main window, and a Message tab in the results window. The message tab is what we're interested in, it will show you the feedback from all the print commands, as well as the actual error that came back when the publish process tried to run the script against an actual database.

Regular errors

These will almost always be something to do with aggregation. For some reason the IntelliSense inside of Visual Studio doesn't catch aggregation issues, and the build process lets them through as well.

The Message tab will probably look something like this:

And what you need to do is scroll right to see the end of that long line I've cut off in that snapshot:

As expected, I've included something in a SELECT and not in the corresponding GROUP BY, and IntelliSense hasn't spotted that until we're actually trying to deploy the code. Easy fix here, add the column to the GROUP BY or, more generally, fix the T-SQL error.

The included file does not exist

This is more usually something you will find when building the project, but sometimes it can sneak in at deployment stage as well. It's something that happens when you've used a file reference in a pre/post deployment file and the referenced file doesn't exist. This should just appear in your error window like this:

This really is a top quality error message, it tells you the file that's missing, it tells you the file that contains the reference, and even the line in the file with that reference. Simple solution here, figure out what's going on with the reference and either make sure the file is there or remove the reference.

Changes to [X] may introduce runtime errors in [Y]

This error occurs because you have an object [Y] in your target database that you have not included in your database project for whatever reason. [Y] includes a reference to another object, [X], that you have included in your database project and that also exists in your target database.

The current deployment will make a change to [X], and the deployment process can see that there in an object [Y] in the target database that may be affected by the change. It cannot, however, see if [Y] will still be in a valid state after the change to [X], because [Y] is not in the database project.

This is not something you can turn off, or work around. The only way to fix this is to bring the project and the target database in line, either by deleting [Y] on the server or bringing [Y] into your project.

This originally started out as a couple of slides in my New Stars of Data talk, but got cut due to time constraints. I was sad to get rid of them because there's some useful stuff there, but they didn't quite fit the theme of the talk and so were dropped in favour of something that did. Anyway, I thought they might make a useful blog post for someone.

kaythintwit.blogspot.com

Source: https://chrisjohnson120.com/2021/03/25/ssdt-database-projects-deployment-error-messages/

Share this post