Finding missing Mandatory Fields in CRM 2011

At present I am working on a large CRM project, we have a complex data migration job to do and we wanted to make sure all mandatory fields are populated when the data load is run.

So.. of with the CRM hat and on with the SQL hat. You could do all this with CRM SDK but why would you?? Native SQL is going to be faster and the following SQL is not going to be part of the project. Its just a tool to help us.

The following SQL will identify system required or business required fields

SELECT
   Entity.Name , Attribute.Name
   FROM Attribute
inner join Entity on Attribute.EntityId = Entity.EntityId
where
AttributeRequiredLevelId in ('systemrequired','required') and
Attribute.VALIDFORCREATEAPI = 1
and Attribute.Name not in ( 
                             'modifiedonbehalfbyyominame' , 
                             'createdonbehalfbyyominame',
                             'createdbyyominame',
                             'modifiedbyyominame',
                             'owneridyominame'
                          )
and isImportable =1
group by Entity.Name , Attribute.Name

I used a cursor to loop over these fields and populated a table variable that I output at the end. The results look like

Entity Field NullRows WSRows SQL   Statement
cust_myent cust_fielda 16 0 select count(*) from cust_myent where cust_fielda is null
cust_myent cust_fieldb 58305 0 select count(*) from cust_myent where cust_fieldb is null
cust_myent cust_fieldc 107 0 select count(*) from cust_myent where cust_fieldc is null

The WSRows is a count of rows that have white space in.

The entire SQL is

set nocount on
declare @entity nvarchar(1024)
declare @field nvarchar(1024)
declare @sql nvarchar(4000)
declare @rowcount int
declare @WSrowcount int
declare @report TABLE ( Entity nvarchar(1024), Field nvarchar(1024),NullRows int,WSRows int,[SQL Statement] nvarchar(1024) )
declare requiredFieldsCursor CURSOR
for
SELECT
Entity.Name , Attribute.Name
FROM Attribute
inner join Entity on Attribute.EntityId = Entity.EntityId
where
AttributeRequiredLevelId in ('systemrequired','required') and
Attribute.VALIDFORCREATEAPI = 1
and Attribute.Name not in ( 'modifiedonbehalfbyyominame' , 'createdonbehalfbyyominame','createdbyyominame','modifiedbyyominame','owneridyominame')
and isImportable =1
group by Entity.Name , Attribute.Name
open requiredFieldsCursor
FETCH NEXT FROM requiredFieldsCursor
INTO @entity, @field
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @time char(8)
SET @time= convert (varchar(30), getdate(), 8)
set @sql = N'select @rowcount = count(*) from ' + @entity + ' where ' + @field + ' is null'
BEGIN TRY
exec sp_executesql @sql, N'@rowcount int out', @rowcount out
set @sql = N'select @WSrowcount = count(*) from ' + @entity + ' where RTRIM(' + @field + ') = '''''
exec sp_executesql @sql, N'@WSrowcount int out', @WSrowcount out
END TRY
BEGIN CATCH
RAISERROR ('%s failed to check %s.%s', 0, 1,@time, @entity,@field) WITH NOWAIT
END CATCH;
set @sql = N'select count(*) from ' + @entity + ' where ' + @field + ' is null'
insert into @report
( Entity , Field ,NullRows,WSRows, [SQL Statement] )
values
( @entity, @field,@rowcount,@WSrowcount,@sql )
SET @time= convert (varchar(30), getdate(), 8)
FETCH NEXT FROM requiredFieldsCursor
INTO @entity, @field
END
select * from @report
where
nullrows > 0 or WSRows > 0
select Entity from @report
where
nullrows > 0 or WSRows > 0
group by Entity
close requiredFieldsCursor
DEALLOCATE requiredFieldsCursor

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s