I've been migrating data for a client recently and part of this has required the use of the MS Data Migration Framework (DMF). I thought I'd post a couple of bugs I found and(thankfully) how I managed to get past them with the use of SQL Profiler!
The Migration Framework works - albeit slowly as it uses the CRM Web Service to stuff the records into the CRM database. The CRM structure I've been trying to migrate to contains a large number of custom fields. The DMF calls many stored procs to get its data (from the CDF database); these are structured by entity e.g
p_dm_GetContact
p_dm_GetContract
I was having a problem with Contacts, and after tracing a migration attempt with cut-down data (for speed), I found that the issue was that the lid was being blown off a VARCHAR(8000) in p_dm_GetContact giving the following SQL Error:
Error: 207, Severity: 16, State: 3
The error is 'Invalid Column Name'. Further investigation revealed that a slightly dodgy piece of code (replicated in all the other similar procs) effectively truncates the dynamically created field list in the 'SELECT' statement...
-- if there are extended fields, then attached them to the end
-- of the base field list
if len(@metadatafields) > 0
set @entityfields = @entityfields + ', ' + rtrim(@metadatafields)
@entityfields contains a comma delimited list of fields from the 'cdf_Contact' table, and @metadatafields contains fields from 'cdf_Contact_Ext' (custom fields). If you concatenate the two you're obviously asking for trouble.
The following shows the 'fix' to enable a bit more room (it's still not very good - but as the code's not available we can't make a nicer fix right now)
CREATE procedure p_dm_GetContact
as
begin
set nocount on
declare @entity varchar(100)
declare @entityfields varchar(8000)
declare @metadatafields varchar(8000)
-- set target entity name
set @entity = 'Contact'
-- query base field list for targeted entity
exec p_dm_GetFields @entity, 'c', @entityfields output
-- query extended field list for targeted entity
exec p_dm_GetMetaDataFields @entity, 'ext', @metadatafields output
-- if there are fields, then attached them to the end
-- of the base field list
if len(@entityfields) > 0
set @entityfields = ', ' + rtrim(@entityfields)
-- if there are extended fields, then attached them to the end
-- of the base field list
------ if len(@metadatafields) > 0
------ set @entityfields = @entityfields + ', ' + rtrim(@metadatafields)
if len(@metadatafields) > 0
set @metadatafields = ', ' + rtrim(@metadatafields)
-- build/execute query statement
-- the status code must be null
exec (' select top 1 '''' As CRMUpdateStatus,
c.ContactId As entityid,
c.StateCode As statecode,
dbo.p_fn_GetID(l.DestinationId) As originatingleadid,
dbo.p_fn_GetID(p.DestinationId) As defaultpriceLevelid,
dbo.p_fn_GetID(a.DestinationId) As accountid,
dbo.p_fn_GetID(su.DestinationId) As owninguser
' + @entityfields + @metadatafields + '
from cdf_Contact c
inner join cdf_Contact_info info
on c.ContactId = info.ContactId
left outer join cdf_Contact_ext ext
on c.ContactId = ext.ContactId
left outer join cdf_Lead_info l
on c.OriginatingLeadId = l.LeadId
left outer join cdf_PriceLevel_info p
on c.DefaultPriceLevelId = p.PriceLevelId
left outer join cdf_Account_info a
on c.AccountId = a.AccountId
inner join cdf_SystemUser_info su
on c.OwningUser = su.SystemUserId
where (info.StatusCode is null)
and (info.DestinationId is null)')
end
GO
Colorized by: CarlosAg.CodeColorizer