SQL Server – Issues I Have With SSDT Based Deployments: Part 4.2

Continuing the section on trying to get a large legacy database imported into an SSDT project that actually builds, we’re at a point now where we should have genuine reference issues to clean up; all our cross database references should be sorted via a database reference to a DACPAC (with or without changing everything to use SQLCMD variables or not).

At this point, we’re going to get really annoyed with all the legacy, simply doesn’t work anyway, code left in our database over years of not tidying up.  Technical debt sucks.

Lets look at some of the things that are going to annoy us!

Genuine Reference Errors.

This is the stuff that is a genuine reference error, from legacy code, and stuff that no longer works.  However, before you get all gung-ho and delete everything that still has reference issues, you need to be aware that SSDT will mark an ambiguous reference when a column appears in an ORDER BY statement and is not aliased; despite this being valid TSQL (see below).  So we’re going to have to go through them all one by one, and either resolve the code, log bugs, mark the script to have “none” as the build action, or just delete them as appropriate.  Tables called things like “NotNeededAnymore”, “DontDropTilYouAskMike” and “aaaaaaaaaa” probably are safe to be binned!  How gung ho you want to be on that, probably depends on which DBA Category you fall into…

One piece of advice though: ensure you check in everything before you start deleting, and do the delete in a single changeset.  You can then make a note of that changeset in case any of this stuff turns out to be needed after all.

Unaliased ORDER BY columns.

I think this is a genuine problem with SSDT; this is all perfectly valid syntax in TSQL.  However, if you have a view and order without aliasing that column in either the select or the order by, and that column name exists in more than one table in your statement.

These ORDER BY references can be fixed by either properly aliasing the column name; e.g. A.Amount AS Amount, or in your ORDER BY clause using the table alias, e.g. ORDER BY A.Amount.  You shouldn’t have to, but, well you’re going to have to.

Nested Cross Database Views with SELECT *

It also seems that nested cross database references to views, e.g. a view in my database that references a view in another database, can’t be resolved if that view has a SELECT * in it.  You shouldn’t have code like that in your production database, but you’re now going to have to sort that mess out!

Warnings that objects differ due to case.

I simply don’t care about this, so you can get shut of these warnings by unticking the “Validate casing on identifiers” option in your Project settings, as per this msdn article.  Bear in mind, you’ll have to reload the project after this, which in my case, takes quite a while….

Building the project

You may find that the project will build well before this stage, but now you should have a genuinely buildable project, that we can go on to look at releasing!