My publish attempts are failing with this error
Msg 205, Level 16, State 1, Line 7572 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Taking the old-school advice to print status messages between deployment script execution, I have narrowed it down to this:
The line number mismatches because of SQL Server quirks, which is why you have to PRINT
stuff like a heathen (see the link above)
Note how there were literally no statements executed. I also had a verbose trace running which confirms that nothing was executed:
How could the control flow statements between two PRINT
statements possibly cause an error?!
Best Answer
Turns out the
GO
statement segregated the successful code from the remainder, which indeed contains an erroneous statement (as per the error message), and so the error was thrown during syntax validation.By taking the line number of the
GO
statement and adding the line number shown in the error message (which is really the offset in the batch), I found a badMERGE
statement, since double-clicking the error message in the SSDT publish results takes me to a random spot in the publish script.So it was caused by statement batching, with syntax validation finally running on a deployment script (that is excluded from building with the SQL project).