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