microsoft crm

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