Error: 8624, Severity: 16, State: 116 -> When things go really wrong

If you are reading about this, it is likely that you just hit the error: Error: 8624, Severity: 16, State: 116

This is an error that means you are having a bad day, because somewhere in your code SQL was generated and that SQL isn't working.

If you haven't already figured out what the SQL is, the pro tip is this: If it the SQL is like 100 lines of joins, then it has some IN statements and in that IN statement is a 100 lines of joins, then that is likely the SQL you are looking for.

If you can't find the SQL with that tip, then go through SQL Server Profiler and figure out where it is.  Remember, somewhere you have a mess that looks something like

SELECT * FROM table join (lots of other stuff()  where mykey in (select mykey from table join (lots of other stuff)) over and over and over.

Now that you have found this code, here are my recommendations:

Figure out how to craft the SQL better so that it isn't using so much logic.

The place that I found this problem was with code that was generated dynamically by other code, and my suggestion is that you fix the underlying problem of having generated unusable code. If you can't do it, you have an option to "just make it run" but this option is going to trade the code working, for very slow code.

You can put an option called "OPTION (FORCE ORDER)" at the end of the code, this will FORCE SQL Server to execute the code with the most naive execution plan possible.  This isn't great, in fact, it is really bad. Any code with that many joins is probably going to be slow even in a perfect world, but it will work.

Here are some good articles I found on this when we hit this problem where I work.

https://blogs.msdn.microsoft.com/sqlserverfaq/2014/07/15/troubleshooting-error-8624-severity-16-state-21-internal-query-processor-error-the-query-processor-could-not-produce-a-query-plan/

https://www.simple-talk.com/sql/performance/controlling-execution-plans-with-hints/