September 16, 2010 at 9:07 am
Hi,
I'm running into several errors trying to use the foreach db function. I have 400+ databases trying to populate a single table in the master table:
I have to run
Error:Msg 208, Level 16, State 1, Line 2
Invalid object name 'crm_leads'.
I would eliminate the database that do not have these table objects but I seem to be running out of space. My other thought is to create a stored procedure for all databases and call that stored procedure using the foreach db. Any thoughts or ideas on this problem. BTW I'm redesigning this whole infrastructure but right now "it is what it is"
Thank You All
DECLARE @command VARCHAR(MAX)
SELECT @command = 'USE [?]
INSERT INTO CRM_AstonishInternal.dbo.AstonishCloseRatioReportSource
( COMPANYID ,
Salesperson ,
LeadDate ,
ContactType ,
Leads ,
InvalidLeads ,
DeletedLeads ,
QuotedLeads ,
ClosedLeads ,
QuotedAuditLeads ,
CloseAuditLeads
)
SELECT act.COMPANYID ,
act.SURNAME + '', '' + act.Name,
CONVERT(VARCHAR, leads.createddate, 101),
CASE WHEN ( contactlookup.[DESCRIPTION] LIKE ''%SEM%'' )
THEN ''Astonish SEM''
WHEN ( contactlookup.[DESCRIPTION] LIKE ''%VIO%'' )
THEN ''Astonish VIO''
WHEN ( contactlookup.[DESCRIPTION] LIKE ''%IQ%'' )
THEN ''Astonish IQ''
ELSE contactlookup.[description]
END,
COUNT(*) Leads,
SUM(CASE WHEN statuslookup.description LIKE ''invalid%'' THEN 1
ELSE 0
END),
SUM(CASE WHEN leads.limbo = 1 THEN 1
ELSE 0
END),
SUM(CASE WHEN statuslookup.description LIKE ''quoted%'' THEN 1
ELSE 0
END),
SUM(CASE WHEN statuslookup.description LIKE ''policy activated%''
THEN 1
ELSE 0
END),
SUM(CASE WHEN ( audit.description LIKE ''quoted%'' ) THEN 1
ELSE 0
END),
SUM(CASE WHEN ( audit.description LIKE ''policy activated%'' )
THEN 1
ELSE 0
END)
FROM crm_leads leads
INNER JOIN CRM_CROSSLEAD cl ON leads.id = cl.LEADID
INNER JOIN CRM_DESCRIPTION statuslookup ON cl.status = statuslookup.K_ID
INNER JOIN CRM_LEADDESCRIPTION contactlookup ON leads.CONTACTYPE = contactlookup.K_ID
INNER JOIN CRM_AstonishInternal.dbo.ACCOUNT act ON cl.SALESPERSON = act.UID
LEFT JOIN ( SELECT a.AuditKeyId ,
a.oldvalue ,
a.newvalue ,
b.DESCRIPTION
FROM dbo.VpcLeadAudit a
INNER JOIN CRM_DESCRIPTION b ON ( ( a.oldvalue = b.K_ID )
OR ( a.newvalue = b.K_ID )
)
WHERE a.AuditType = 1
AND a.fieldname = ''Status''
AND ( ( b.description LIKE ''quoted%'' )
OR ( b.description LIKE ''policy activated%'' )
)
) Audit ON leads.id = audit.AuditKeyId
WHERE contactlookup.K_ID IN ( 12, 11, 1454, 706, 1, 4, 690, 2367,
2837, 4173, 707, 8511 )
AND contactlookup.type = 10
AND contactlookup.LANG = ''EN''
GROUP BY act.SURNAME + '', '' + act.Name ,
act.COMPANYID ,
CONVERT(VARCHAR, leads.createddate, 101) ,
CASE WHEN ( contactlookup.[DESCRIPTION] LIKE ''%SEM%'' )
THEN ''Astonish SEM''
WHEN ( contactlookup.[DESCRIPTION] LIKE ''%VIO%'' )
THEN ''Astonish VIO''
WHEN ( contactlookup.[DESCRIPTION] LIKE ''%IQ%'' )
THEN ''Astonish IQ''
ELSE contactlookup.[description]
END
ORDER BY act.SURNAME + '', '' + act.NAME'
--PRINT @command
EXEC sp_MSforeachdb @command
September 16, 2010 at 9:10 am
You could wrap the insert in a try-catch, and then it would at least continue on to the rest of the databases. Then insert the error into a logging table, and handle each of those databases individually (either add the missing table..or whatever your solution for this is).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply