GoldMine CONTACT2 Data Integrity Checks 2014

GoldMine CONTACT2 Data Integrity Checks.sql

-- ((( CONTACT2 Data Integrity Checks )))
 
/* --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 
CONTACT2 Integrity Check
 
ALL of the following queries should return NO results. If ANY results are returned, then something is wrong.
 
If the first query returns results, here are your options:
 
Determine if the fields listed TRULY are needed in GoldMine.
 
If the fields ARE needed, then add them to the GoldMine interface.
 
If the fields are NOT needed, then delete them from the CONTACT2 structure, via SQL Server Management Studio, OR via the following query:
 
ALTER TABLE CONTACT2 DROP COLUMN column1, column2, etc.
 
If the second query returns results, here are your options:
 
Determine if the fields listed TRULY are needed in GoldMine.
 
If the fields are NOT needed, then delete them from the GoldMine interface.
 
If the fields ARE needed, then ADD them to the CONTACT2 structure, via SQL Server Management Studio.
 
If the third query returns results, then you must scrutinize the data type discrepancies, in detail, and resolve accordingly.
 
If the fourth query returns results, use the noted deletion query to get rid of the orphans.
 
If the fifth query returns results, then detailed clean-up is required. Contact a qualified GoldMine consultant.
*/ --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 
-- Identify columns in CONTACT2 which are not defined in CONTUDEF
select * from syscolumns left join contudef on
(syscolumns.name = contudef.field_name and contudef.dbfname = 'CONTACT2')
where id in (select id from sysobjects where name = 'CONTACT2' and type = 'U')
and contudef.recid is null and name <> 'RECID' order by name
 
-- Identify columns defined in CONTUDEF which are not in CONTACT2
select * from contudef left join syscolumns on
(contudef.field_name = syscolumns.name and id in (select id from sysobjects where name = 'CONTACT2' and type = 'U'))
where dbfname = 'CONTACT2' and syscolumns.name is null order by field_name
 
-- delete from contudef where dbfname = 'CONTACT2' and field_name = 'MERGECODES'
 
-- Identify overall data type discrepancies between GM CONTUDEF and SQL CONTACT2
select syscolumns.name,
systypes.name as 'SQL_Type',
CASE WHEN systypes.name = 'bigint' THEN 'N'
WHEN systypes.name = 'bigint' THEN 'N'
WHEN systypes.name = 'char' THEN 'C'
WHEN systypes.name = 'datetime' THEN 'D'
WHEN systypes.name = 'decimal' THEN 'N'
WHEN systypes.name = 'float' THEN 'N'
WHEN systypes.name = 'int' THEN 'N'
WHEN systypes.name = 'money' THEN 'N'
WHEN systypes.name = 'numeric' THEN 'N'
WHEN systypes.name = 'real' THEN 'N'
WHEN systypes.name = 'smalldatetime' THEN 'D'
WHEN systypes.name = 'smallint' THEN 'N'
WHEN systypes.name = 'smallmoney' THEN 'N'
WHEN systypes.name = 'tinyint' THEN 'N'
WHEN systypes.name = 'varchar' THEN 'C'
ELSE 'X' END as 'Match_Type',
contudef.field_type as 'GM_Type',
syscolumns.length as 'SQL_Length',
contudef.field_len as 'GM_Length',
contudef.field_dec as 'GM_Decimals'
from syscolumns
left join contudef on (syscolumns.name = contudef.field_name and contudef.dbfname = 'CONTACT2')
left join systypes on (syscolumns.xtype = systypes.xtype)
where id in (select id from sysobjects where name = 'CONTACT2' and type = 'U')
and syscolumns.name like 'U%' and contudef.recid is not null
and contudef.field_type <>
CASE WHEN systypes.name = 'bigint' THEN 'N'
WHEN systypes.name = 'bigint' THEN 'N'
WHEN systypes.name = 'char' THEN 'C'
WHEN systypes.name = 'datetime' THEN 'D'
WHEN systypes.name = 'decimal' THEN 'N'
WHEN systypes.name = 'float' THEN 'N'
WHEN systypes.name = 'int' THEN 'N'
WHEN systypes.name = 'money' THEN 'N'
WHEN systypes.name = 'numeric' THEN 'N'
WHEN systypes.name = 'real' THEN 'N'
WHEN systypes.name = 'smalldatetime' THEN 'D'
WHEN systypes.name = 'smallint' THEN 'N'
WHEN systypes.name = 'smallmoney' THEN 'N'
WHEN systypes.name = 'tinyint' THEN 'N'
WHEN systypes.name = 'varchar' THEN 'C'
ELSE 'X' END
order by syscolumns.name
 
-- CONTACT2 Orphans
select * from contact2 where accountno not in (select accountno from contact1)
-- delete from contact2 where accountno not in (select accountno from contact1)
 
-- CONTACT2 Duplicates
select distinct accountno, count(*) from contact2 group by accountno having count(*) > 1 order by accountno
-- select recid, * from contact2 where accountno in (select distinct accountno from contact2 group by accountno having count(*) > 1) order by accountno, recid
 
-- ( end )