Okay, it was foolish for me to have posted confusing code. 🙂
This is not any actual business logic that exists, but rather is a simplified example that causes the error. I had to leave urgently and was describing all of the things I could think of to test. All of my real life statements causing the issue had joins with the temp table, and I hadn't tested a straight select from the newly created object. I've now done a little more testing...
This still receives the error:
SELECT *
INTO #Announcements
FROM Announcements;
SELECT *
FROM #Announcements;
I get the error ANY time I create a new table and try to reference that table in the same transaction. Semicolons don't fix it, but GO statements do. Whether I use temp or permanent tables doesn't matter.
I found it odd (and possibly very revealing for those who know what happens behind the scenes) that Activity Monitor immediately produces the same error, as though it's opening generates a table it reads from.
Anyway, I regretfully didn't post a question at all with my original post but left it implied. What I may need here is DBA type assistance, whereas I only know T-SQL development and have no DBA resource available.
I did a CHECKDB, but considering I get the behavior on all databases, it's no surprise that it didn't help. I don't know what other kind of script/commands to do to test why this is not working, when it has been working for years, literally.
I restarted the services with no success. I will be rebooting tonight, but other than that, I'm kind of lost.