In Microsoft Data Services (MDS), when running a stored procedure to process a staging table (stg.udp_name_Leaf), I received this error:

“Cannot insert the value NULL into column ‘Code’, table ‘MDS2012.mdm.tblStgErrorDetail’; column does not allow nulls. INSERT fails.”

As described in the Connect item udpEntityStagingCreateLeafStoredProcedure Create Code values automatically Error, this is a bug that will be fixed in SQL Server 2012 Service Pack 1.

The issue is that there is a bug in the stored procedure when an Entity has been marked for “Create Code values automatically”.  If you insert a member and one of the attributes is a domain attribute that doesn’t exist, you will get the above error.  What is happening is that the code is null when trying to insert into [mdm].[tblStgErrorDetail] and the reason for it being null is that it hasn’t been generated yet.

In my case I was processing an entity called TblFacility and putting values in a foreign key field called FacilityOwnership_FK, which is a domain-based attribute linking to an entity called RefOwnershipType, and RefOwnershipType was empty.

Note you can run the stored procedure stg.udp_Name_Leaf (i.e. stg.udp_TblFacility_Leaf) in SSMS to see the line number it bombs on to find out which foreign key is having the problem.