Post date: Nov 09, 2011 10:40:57 PM
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)
WHERE
SubscribedObject = 'ContactMethod'
AND NOT EXISTS (
SELECT ContactMethod.ContactMethodID
FROM ContactMethod WITH (NOLOCK)
WHERE ContactMethodID = ObjectSubscription.SubscribedObjectID
)
)