Troubleshooting‎ > ‎

Import Failures: "Cannot insert NULL into column '{Table}ID'"

posted Nov 9, 2011, 2:40 PM by Wes Coulter   [ updated Dec 13, 2011, 7:34 AM by Eric Patrick ]
When attempting to import ContactMethods, a sporadic error was being produced:

Error executing Stored Procedure pContactMethodInsert; Cannot insert NULL into column 'ContactID'

The ultimate cause of this error was orphaned ObjectSubscription (OS) rows. OS rows had been created in the past, then the corresponding ContactMethod rows were deleted, without the OS rows having been deleted. All tables that may be OS-bound (e.g. participate in the Import Framework by existing in the ObjectFactory.config file) should have a SubscriptionDelete trigger. If the table is missing this trigger, it should be created and added to source control.

The following query can be used to locate orphaned ObjectSubscription rows, and delete them:

--DELETE FROM ObjectSubscription WHERE ObjectSubscriptionID IN 
SELECT ObjectSubscriptionID
FROM ObjectSubscription WITH (NOLOCK)
SubscribedObject = 'ContactMethod'
SELECT ContactMethod.ContactMethodID
FROM ContactMethod WITH (NOLOCK)
WHERE ContactMethodID = ObjectSubscription.SubscribedObjectID