TJ Martin's blog

If you remember from this previous posting: http://blogs.salentica.com/tmartin/2011/07/04/crm-4-0-0×80040216-an-unexpected-error-occurred/

We found that this was due to the AddressNumber=1 field in the database having NULL values, and resolved it correspondingly.

Now, however, another client faced a similar issue but albeit different one altogether. In the Contact record, address2 fields are being used but certain records aren’t able to be updated when attempting to modify these fields. CRM throws a generic, non-descript error.

Upon running a platform trace to reproduce the issue, we again see the following from the logs:

Stack Trace Info: [InvalidCastException: Specified cast is not valid.]

at Microsoft.Crm.BusinessEntities.AddressTrigger.Update(GUID)

Diving into SQL, we looked at a few affected records in the CustomerAddressBase table and compared it with a test record that works and can be updated just fine.

From the looks of it, AddressNumber=2 field simply didn’t exist in the database for the affected records.

Now, as with the previous posting, the supported way of resolving this issue is to recreate the records or merge them into a new one. However, if there are too many records to do this for (READ: hundreds), you may want to look into an unsupported method. By unsupported, we mean that Microsoft will NOT support it and neither will we.

But for the curious, we ran the below script to insert an empty AddressNumber=2 field into the affected records.

————————–
–Add CustomerAddress
– AddressNumber =2 if missing
– 2 Separate Steps
————————-

———-
– Step 1
———-
declare @ThisUserId uniqueidentifier
select @ThisUserId = SystemUserId from SystemUser where DomainName=”

declare @RunDate datetime
select @RunDate = GETUTCDATE()

insert into CustomerAddressBase
([CustomerAddressId]
,[ParentId]
,[CreatedOn]
,[CreatedBy]
,[ModifiedOn]
,[ModifiedBy]
,[AddressNumber]
,[ObjectTypeCode]
,[Name]
,[ImportSequenceNumber]
)
select NEWID(),
ParentId,
@RunDate,
@ThisUserId,
@RunDate,
@ThisUserId,
2,
2,
‘Billing’,
999
from CustomerAddress
where ParentId not in (select ParentId from CustomerAddress where AddressNumber = 2)
and ObjectTypeCode = 2
group by ParentId

——
–Step 2 – ONLY After Step 1 completed successfully
——

insert into CustomerAddressExtensionBase
(CustomerAddressId,
ssi_primarymailingaddress
)
select CustomerAddressID,
0
from CustomerAddressBase
where CustomerAddressId not in (select customeraddressid from CustomerAddressExtensionBase)

The result should look like the below, and you should be back to updating the records correctly.