1

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:

enter image description here

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:

enter image description here

How could the control flow statements between two PRINT statements possibly cause an error?!

  • I was going to say, line 7572 in the error message does not match line 31890 that you've highlighted in the screen shot. – Aaron Bertrand Jul 18 at 19:18
  • Which is yet another reason why debugging SQL Server issues is infuriating. I have updated my post to mention this giant violation of the Principle of Least Astonishment. – Elaskanator Jul 18 at 20:08
  • I think newer versions of SSMS address this issue but I don’t know if/when they will get adopted in SSDT. – Aaron Bertrand Jul 18 at 20:13
2

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 bad MERGE 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).

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.