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.
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.
Labels: debugging, Level 16, Line 1 Attempting to set a non-NULL-able column's value to NULL., Msg 681, SQL Server 2008 Errors, State 3
posted by TedS at 8:47 AM
0 Comments:
Post a Comment
<< Home