Very Large Transaction Causing Error 'Attempting to set a non-NULL-able column's value to NULL'

Question

Very Large Transaction Causing Error 'Attempting to set a non-NULL-able column's value to NULL'

mjoc69

SSC Veteran

Points: 237
More actions
December 16, 2016 at 2:09 am

#332824

I hope someone can help.
Using SQL Server 2008 R2 with SP2 (10.50.4042.0) I am getting the error 'Attempting to set a non-NULL-able column's value to NULL' when executing a large transaction targeting many tables.
The t-sql script attempts to identify data in a source database that is missing or outdated in a target database and then issues the relevant inserts or updates against the target database only.
Each transaction attempt is batched using the DATEPART function. When batched for DayOfYear, Week, or Month the attempted transaction functions as expected. As soon as I attempt to batch for Quarter or Year then the error occurs. I'm assuming that the transaction fails due to data volume as I do not get the error when the transaction block is removed.
Both source and target database have SNAPSHOT_ISOLATION ON by default but have also tried with this option set to OFF.
I've tried disabling the FK's at the point of failure but this doesn't help. However when I disable all FK's in the source database the transaction completes successfully, although very slowly (I presume because the query plans can not be built optimally due to the constraints no longer being trusted)
pseudo code is
WHILE
BEGIN
Determine Next Batch based on DATEPART function
BEGIN TRAN
BEGIN TRY
MERGE Something 1
MERGE Something 2
MERGE Something 3
MERGE Something 4
MERGE Something ...
DELETE Something 10 from target
DELETE Something 11 from target
DELETE Something 12 from target
DELETE Something 13 from target
INSERT Something 10 into target
INSERT Something 11 into target
INSERT Something 12 into target
INSERT Something 13 into target
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
IF TRANCOUNT > 0
COMMIT
END -- while (do next batch)
The problem occurs at the first insert after the deletes and this is also where the volume of records can really begins to be quite considerable.
I've checked the souce data for nulls at the point of failure and found nothing.
Would really appreciate any assistance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

I don't know how you expect us to help you troubleshoot this. The problem specifically says that you have a NULL value, but you haven't given any us any data to work with, so we can't tell where that NULL value might be coming from.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

If you're inserting into a non-nullable column from a value that could be null, ALWAYS use ISNULL() or COALESCE().

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

I've always found that error to be misleading and is usually a result (in my experience anyhow) of not including a record for a non-nullable column. This will give me that error:

DECLARE @table TABLE (col1 int NOT NULL);

INSERT @table (col1) VALUES (1),(NULL);

Here it's easy to see what the problem is. This, however, will also get me the same error:

DECLARE @table TABLE (someid int NOT NULL, col1 int, col2 int);

INSERT @table (col1, col2) VALUES (1,2),(5,6);

Here the actual error reads,

Msg 515, Level 16, State 2, Line 23

Cannot insert the value NULL into column 'someid', table '@table'; column does not allow nulls. INSERT fails..

What's nice is, at least this error calls out the column in question.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001