Unable to delete Contacts in Business Contact Manager

Recently a problem occurred where a client was unable to delete contacts or accounts in business contact manager. The following error was displayed;

Error Converting Data type int to smallint.

The problem can be corrected with a change to one stored procedure on the SQL server called dbo.UpdatePhoneLogs, the corrected storedprocedure is below. Only two small changes are required at the top of the code stack. On lines 11 & 12. change the variable type from smallint to int. This is caused by the 32,000 limit of smallints in SQL.

USE [BCMPROD]
GO
/****** Object:  StoredProcedure [dbo].[UpdatePhoneLogs]    Script Date: 11/27/2012 10:05:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[UpdatePhoneLogs]
(
	@ContactServiceID int,
	@GrandParentContactServiceID int,
	@multiplier smallint
)
As
Begin

	DECLARE @Version bigint
	SELECT @Version = ClientDataVersion from OrgTable
	DECLARE @UpdatedCampaigns TABLE (ActivityID int NOT NULL PRIMARY KEY, ActivityGUID uniqueidentifier not null, NPhoneLogs int, IsDeletedLocally bit)

	Insert into @UpdatedCampaigns (ActivityID, ActivityGUID, IsDeletedLocally, NPhoneLogs)
	SELECT
		at_campaign.ActivityID,
		at_campaign.ActivityGUID,
		at_campaign.IsDeletedLocally,
		COUNT(*)
	FROM
		dbo.ActivitiesTable at_campaign
		INNER JOIN dbo.ActivitiesTable at ON at_campaign.ActivityGUID = at.ReferredEntryId
		INNER JOIN dbo.ActivityContacts ac ON at.ActivityID = ac.ActivityID
	WHERE
		(ac.ContactID = @ContactServiceID OR ac.GrandParentContactServiceID = @ContactServiceID)
		AND at_campaign.ActivityType = 21
		AND at.ActivityType = 15
		AND at.ReferredEntryId IS NOT NULL
	GROUP BY
		at_campaign.ActivityID, at_campaign.ActivityGUID, at_campaign.IsDeletedLocally

	UPDATE dbo.CampaignTable SET
		NumberPhoneLogs = ISNULL(NumberPhoneLogs, 0) + @multiplier * uc.NPhoneLogs
	FROM
	dbo.CampaignTable ct INNER JOIN @UpdatedCampaigns uc
	ON ct.ActivityID = uc.ActivityID

	-- Add campaign to change queue (assumes client data version has already been bumped)
	-- FolderKind.Campaign ItemType.Campaign ChangeType.Modified
	Insert Into dbo.ChangeQueue (EntryGUID, EntityID, ViewRowID, FolderType, ItemType, Operation, Version, ChangeUser, ChangeTime)
	(SELECT uc.ActivityGUID,
		uc.ActivityID,
		uc.ActivityID,
		dbo.fn_IsDeletedToCampaignFolderType(IsDeletedLocally),
		0x1B,
		5,
		@Version,
		suser_sname(),
		current_timestamp
		FROM @UpdatedCampaigns uc)
End
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s