The category "all in a day's work" is a fairly ambiguous description. I say it tongue-in-cheek though with a large hint of sarcasm and cynicism because when I think of the phrase it always brings to mind something [else] that f'd up [again]. That's just me though. So here we go...

We have a SQL [2000] stored procedure that [in a nutshell] coallates a butt-load of information, updates some summary tables for web presentation and then generates trouble tickets for problem issues. This stored proc had been working great for 8 months. No issues, no errors, no glitches. All of a sudden on 12.19.2005 it decided to start throwing an error while inserting rows into a temporary table created within the SP: "Internal SQL Server error. [SQLSTATE 42000] (Error 8624)" WTF? We hadn't made any code changes (SQL or otherwise) that affected this stored proc. One day, it just up and decided to stop working. Gooo-reeeaat. [groan]

Of course, one of the first things I do is a search of MSDN for that error. As you can see this is quite a common problem and the explanations cover a pretty wide area, but mi' compadre found an article that referenced the problem being caused by a join in a subquery on the original table. So she pulled that subquery out of the join statement, created a temporary table with the information and then added a join to the temp table in the original insert statement. It worked great for a week. Then [again] it just up and decided to stop working this past Sunday (1.2). Gooo-reeeaat.

At this point I'm kind of at my wit's end. My co-worker had spent a day researching this problem and finding the resolution while I was working on other things (just to be clear, she's smart as a whip and a very dedicated worker...so when she spent a day figuring out the problem I knew she wasn't just twiddling her thumbs during that time.). Unfortunately [for me] she was out yesterday which meant that I had to deal with the issue. After about an hour of playing around with the INSERT and SELECT statements I hit on something. Here are the facts:

• The original stored procedure created the temporary table and then performed an INSERT into that table using a SELECT statement with several (10+) joins.
• The INSERT statement was now consistently failing with the error: Server: Msg 8624, Level 16, State 1, Line x Internal SQL Server error.
• The SELECT statement used for the INSERT would consistently work when run on it's own (not trying to insert rows into the temporary table)

Just for sh*ts and giggles, I decided to cut out the temp table creation and re-write the INSERT...SELECT...FROM statement to a SELECT...INTO...FROM statement so that the temporary table was being generated during the SELECT...INTO statement...and it worked...consistently! Weird.

I honestly have no idea why the SELECT...INTO statement works [for now] and the INSERT...SELECT statement doesn't [after it did for months]. It just doesn't make any sense to me because it's simply two different ways of doing the same exact thing. Kind of like driving to Sea-Tac...coming from the North you can take I-5 or I-405. Either one will get you there it's just a different route. Weird.

Anyway, a pretty typical [sh*t's broke again] day to start off 2006.

~tod