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 is SQL Server MVP and a independent consultant with the title of Business Intelligence/Data Warehouse/Master Data Management Architect and Developer, specializing in the Microsoft SQL Server BI stack.
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>