Another error not as it seems

The Error: Msg 1934, Level 16, State 1, Line 1 ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

 I was attempting to do a simple change
 Alter Table TABLENAME  Alter Column COLUMNNAME BIGINT NOT NULL 

When I got the error above. I was floored, I set ANSI_Warnings every which way but loose. Then I read the error more closely. I dropped the non-clustered index on the computed columns and tried again. No dice. So I looked closer. and decided, well I don't need the computed columns. I dropped the computed columns and then I was able to alter the table. I'm guessing there is a bug in SQL Server 2008 R2 RTM and SP2 (I build a special environment with just this one table). And, yes, I do need a bigint here. This table ran out of ints in less than a year.

1

Nulls, bad data and execution plans

Msg 681, Level 16, State 3, Line 1
Attempting to set a non-NULL-able column's value to NULL.

That is the error I recieved one morning on a job that has run with success for many moons. There were no non-null-able columns in the dest table at all. Which made things seem very odd. Then I saw a couple KB articles from Microsoft that seemed to point to one of two hot fixes. One was included in SP1, and we had applied that months ago. The other was in CU 7 to SP1... great.

I wasted an entire day trying to debug it and was even tempted to put SQL 2008 SP2 CTP in production without testing *GASP* The issue for my company was huge, big reporting implications and what not.

I went down a route that had me avoiding the linked server which, actually, worked in small scale testing. I was able to do between 500,000 and 1,000,000 rows a 'new' way of moving the data around and I wasn't able to do 1 the old way. I thought I had it licked. It was, in my mind, simply a new execution plan that caused hash joins on a nullable field rather than what it was doing before that worked, and I had a work around.

SOOOOO I released the new code and waited for the process to start. I woke up to the SAME error.

Arrrrrgggghhhhhhh (and no it isn't talk like a pirate day)

Then one of the data guys 'fessed up to doing some new ETL processes on two of the tables that were in question... and one of them, guess what, had NULLS in a field used for a join. Excellent. I found the nulls, 'fixed' them and re-ran the process.

KABLAMO. But this time I got a new error. turns out that the last data folks here decided to use a char(1) as a tiny int, and somehow this new ETL process put a '*' into the char(1) field. Once that was fixed all was well.


So what are the lessons here?
1) Error Messages aren't what they seem
2) While KB articles may not be 100% accurate, they often point to the right place. Follow them
3) What changed sometimes IS the data. Just because nobody did a code release doesn't mean that things didn't change.