March 26, 2007 at 1:38 pm
I need to do a pull in each db.... Thanks in advance for help...
---------------------
Server: Msg 55555, Level 16, State 1, Procedure sp_MSforeach_worker, Line 79
sp_MSforeach_worker assert failed: command too long
(0 row(s) affected)
---------------------------------------------------
The query below was running fine, but when I added
======
if exists (select [?].dbo.* from sysobjects where [?].dbo.[name] = ''Communication'' and [?].dbo.xtype = ''u'')
begin
========================== I got the error above.
Insert into EmailStats
EXEC sp_MSForEachDB 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
if exists (select [?].dbo.* from sysobjects where [?].dbo.[name] = ''Communication'' and [?].dbo.xtype = ''u'')
begin
select distinct ''[?]'',
a.*, b.Opened, b.HardBounced, b.SoftBounced, b.MalFormed, c.Unsubscribed from (
select distinct
act.iniUser,
ii.[name] as IssueName,
c.[name] as CampaignName,
p.[name] as ProjectName
from [?].dbo.individual1 i
inner join [?].dbo.project p
on a.projectFK = p.[id]
inner join [?].dbo.campaign c
on p.campaignFK = c.[id]
inner join [?].dbo.issue ii
on c.issueFK = ii.[id]
where
convert(varchar(10),cc.iniDate,112) >= ''20070101''
group by
ii.[name],
c.[name],
p.[name]
) a
left JOIN (
select M.ActionFK,
case when mbsStatus = 9 then Cnt else 0 end as Opened,
case when mbsStatus = 5 then Cnt else 0 end as HardBounced,
case when mbsStatus = 6 then Cnt else 0 end as SoftBounced,
case when mbsStatus = 7 then Cnt else 0 end as MalFormed
from
(select c.actionFK,
mbsStatusTypeFK as mbsStatus,
count(c.[id]) as cnt
from [?].dbo.communication c
where convert(varchar(10),c.iniDate,112) >= ''20070101''
group by c.actionFK,
mbsStatusTypeFK )M
) b
on a.ACtionID = b.ActionFK
left join
(select action_fk, count(ind_fk) as Unsubscribed
from [?].dbo.unsubscribe
group by action_fk ) c
on a.ActionID = c.Action_fk
end
'
-- select * From emailStats
March 27, 2007 at 3:04 am
Looking at the sp_MSfor... procedures there seems to be a limit on the number of placholders ('?'s) you can use in the command string of 9. It looks like you have exceeded that with your latest change.
That's the problem with using undocumented features, the limits are also undocumented.
September 12, 2012 at 1:39 am
Yes absolutely it was because of exceeding the maximum number of Placeholders inside of sp_msforeachtable or sp_msforeachdb. i have got the same problem but i have just replaced that placeholder(?) with a variable and i have used that variable over my entire script that works fine for me.:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply