MDS Error: “Cannot insert the value NULL into column ‘Code'”

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.

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in MDS/MDM, SQLServerPedia Syndication. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>