check for bad foodhandler links

select count(*) as badrecords from tbl_doc_documents a with (nolock) inner join tbl_fsc_content b with (nolock) on b.contentid = a.contentid where mainfolder <> fsl_tablecode + convert(varchar(10), ownerid) and mainfolder = 'MFR957' Found #qcf_badData.badrecords# bad content links, caused by foodhandler packaging system. Query: select * from tbl_doc_documents a with (nolock), tbl_fsc_content b with (nolock) where a.contentid = b.contentid and mainfolder <> fsl_tablecode + convert(varchar(10), ownerid) and mainfolder = 'MFR957' select count(*) as badrecords from tbl_PRD_SKUs with (nolock) where (ProdID is null or ProdID = 0) and inactive = 0 and FSL_TableCode = 'bro' SELECT OwnerID, FSL_TableCode, mfrid, mfr.mfr_name, product, COUNT(*) as dupcount, case when FSL_TableCode = 'bro' then (select broker_name from tblBrokers with (nolock) where broker_id = prd.ownerid) when FSL_TableCode = 'mfr' then (select mfr_name from tblManufacturers with (nolock) where MFR_ID = prd.OwnerID) else '' end as catalogOwner FROM tbl_PRD_Products prd with (nolock) INNER JOIN tblmanufacturers mfr with (nolock) ON mfr.mfr_id = prd.mfrid WHERE FSL_TableCode = 'BRO' AND mfrid IS NOT NULL AND product IS NOT NULL GROUP BY OwnerID, FSL_TableCode, mfrid, mfr.mfr_name, product HAVING COUNT(*) > 1 ORDER BY OwnerID, FSL_TableCode, mfrid, product

check for objectives with single character in OprTypeRestrict field

select * from tbl_BCRM_Objectives with (nolock) where len(rtrim(OprTypeRestrict)) = 0 Found #qcf_badData.recordcount# Objectives with space in OprTypeRestrict field. Query: select * from tbl_BCRM_Objectives with (nolock) where len(rtrim(OprTypeRestrict)) = 0

check for duplicate email recipients in tbl_eml_campaignrecipients - null timesubmitted

select cam.name, cam.campaignid, cam.rundate from tbl_EML_Campaign cam with (nolock) where CONVERT(DATE,cam.RunDate) = '#dateformat(now(),"mm/dd/yyyy")#' and cam.StatusCD = 'suc' and exists ( select recipientid from tbl_EML_CampaignRecipients with (nolock) where CampaignID = cam.campaignid and TimeSubmitted is null ) The following email campaigns ran today and resulted in recipients with NULL TimeSubmitted: CampaignID: #qcf_badData.campaignid# - #qcf_badData.name# Query: select cam.name, cam.campaignid, cam.rundate from tbl_EML_Campaign cam with (nolock) where CONVERT(DATE,cam.RunDate) = '#dateformat(now(),"mm/dd/yyyy")#' and cam.StatusCD = 'suc' and exists ( select recipientid from tbl_EML_CampaignRecipients with (nolock) where CampaignID = cam.campaignid and TimeSubmitted is null )

check for inactive Skus

SELECT s.ownerid, s.fsl_tableCode, s.Sku, s.Skudesc, s.inactive, CompanyName = CASE WHEN s.fsl_tableCode = 'BRO' THEN (select broker_name from tblbrokers with (nolock) where broker_id = s.ownerid) WHEN s.fsl_tableCode = 'MFR' THEN (select mfr_name from tblmanufacturers with (nolock) where mfr_id = s.ownerid) ELSE (select 'Undefined Account Name') END FROM tbl_PRD_Skus s with (nolock) WHERE s.inactive = 1 ORDER BY CompanyName The following #numberformat(qcf_badData.recordCount)# product catalog sku(s) have been erroniously marked as INACTIVE: Query: SELECT s.ownerid, s.fsl_tableCode, s.Sku, s.Skudesc, s.inactive, CompanyName = CASE WHEN s.fsl_tableCode = 'BRO' THEN (select broker_name from tblbrokers with (nolock) where broker_id = s.ownerid) WHEN s.fsl_tableCode = 'MFR' THEN (select mfr_name from tblmanufacturers with (nolock) where mfr_id = s.ownerid) ELSE (select 'Undefined Account Name') END FROM tbl_PRD_Skus s with (nolock) WHERE s.inactive = 1 ORDER BY CompanyName

added check for record 755 in tbl_fsc_content. record was missing today and caused problems!

select * from tbl_FSC_Content with (nolock) where ContentID = 755 Table tbl_FSC_content is missing record 755 (clearpixel.gif)!! This needs to be fixed ASAP. RECORD VALUES SHOULD BE --------------------------------------------- contentId: 755 mainFolder: generic subFolder: UTL sunFolder2: NULL fileName: clearpixel.gif 1) QUERY TO PUT IT BACK --------------------------------------------- SET IDENTITY_INSERT tbl_fsc_content ON INSERT INTO tbl_fsc_content (contentid, mainfolder, subfolder, subfolder2, filename) VALUES (755, 'generic', 'UTL', NULL, 'clearpixel.gif') SET IDENTITY_INSERT tbl_fsc_content OFF 2) CHECK TO SEE IF CLEARPIXEL.GIF WAS DELETED FROM GENERIC/UTL FOLDER. IF SO, COPY IF FROM GENERIC FOLDER TO GENERIC/UTL FOLDER.

#htmleditformat( arguments.text )#