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
[code language=”SQL”]
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
[/code]
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
[code language=”SQL”]
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[/code]
