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

We install everything via scripts so that means for me to roll out the fix to production I had to code the update in transact SQL.  Add to that the fact that I cannot lose any data from the table made the process a little more interesting than I had expected.  I can do this through the GUI in 10 seconds flat or via T-SQL when first creating the table column, but I've never had to do it to an existing table via T-SQL.  Let me tell you...I had a hell of a time trying to figure out the syntax from SQL Books Online.  In fact, I didn't. 

1. IF OBJECTPROPERTY(OBJECT_ID('TableName_Here'), 'TableHasDefaultCnst') = 0
2. BEGIN
3.         ALTER TABLE TableName_Here WITH NOCHECK ADD CONSTRAINT [ConstraintName] DEFAULT 'DefaultValue_Here' FOR ColumnName_Here
4. END   

It's been quite awhile since I posted a how2 entry, which are consistently my biggest source of traffic from searches...so here goes another...

A few weeks ago I was looking to create a table in an ASP.NET page with one of the columns containing the result set of multiple rows from one of the SQL tables.  Basically, I wanted to concatenate multiple rows returned from a T-SQL query into one long string and dump that into the ASP.NET dataset.  Make sense?  Probably not so here's an example:

SqlTable1 contains two columns, Team and Person.  A person can be a member of multiple teams so a select * from SqlTable1 might look like this:

Team - Person
A - Mel
A - Danny
A - Rene
B - Mel
B - Danny
B - Joe
C - Rene
C - Chris

And I want to return this in my ASP.NET dataset:

Team - Members
A - Mel, Danny, Rene
B - Mel, Danny, Joe
C - Rene, Chris

Hopefully that's clear now.  :)  You would think that a powerful database application like SQL 2000 would be readily capable of something like this and it is, but it wasn't obvious to me (or most of the internet) how to do it.

I did several searches via MSN Search and Google and finally ended up implementing this solution using a temporary table and a staging table in SQL to hold my results.  Very ugly and hackish, but it works.

Then a few days later Emery (my cohort in crime at work) and I were looking over my code when he shared the 'easy' way with me (he had just learned it himself a few weeks before).

DECLARE @list VARCHAR(8000)
SELECT @list = ISNULL(@list,") + ',' + Person
FROM SqlTable1
WHERE Team = 'A'

If you perform a SELECT @list it will return "Mel, Danny, Rene."  You can even wrap this up in a SQL function if you like.

Hope that helps!

~tod