Client Status Reporting workaround for "subquery returned more than 1 value" in chservice.log
After you have installed Client Status Reporting in R2, when trying to update client status in the ConfigMgr database you might see
an error similar to this in the chservice.log
Invalid SQL error - Subquery returned more than 1 value. This is not
permitted when the subquery follows =, !=, <, <= , >, >= or when the
subquery is used as an expression.
The statement has been
terminated.
Warning: Null value is eliminated by an aggregate or other SET
operation.
This is typically followed by
Failed to sync client summary in database
I have seen this happen when you have overlapping boundaries (or other
problems) and are trying to run the ch_SyncClientSummary stored procedure. This
is the SP that updates the actual client status in the CH_ClientSummary table.
If you have overlapping boundaries - or perhaps duplicate IDs, the subselect
will return more than 1 row per client. This will cause the client status sync
to fail, and basically renders the Client Status Reporting useless...
But, you can modify the ch_SyncClientSummary stored procedure.
This is not a supported solution, so back up your data
first and perform it on a test system. Basically, we are going to tell the
stored procedure to only pull back the "Top 1" in the subselect. That way, we
are guaranteeing we only see 1 row returned.
Replace your database name below if it is not "SMS".
Replace the text in the ch_SyncClientSummary stored procedure with this:
/******************CODE BELOW HERE********************************/
USE [SMS]
GO
/****** Object: StoredProcedure
[dbo].[CH_SyncClientSummary] Script Date: 12/10/2008 09:05:59 ******/
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE
[dbo].[CH_SyncClientSummary]
AS
BEGIN
SET NOCOUNT ON
declare @UTCdiff int
set @UTCdiff = DateDiff(mi, GetDate(),
GetUTCDate())
-- Add a new record for every machine if it doesn't
already exist
Insert into CH_ClientSummary(MachineID)
Select
MachineID
From MachineIdGroupXRef
Where not exists (select *
from CH_ClientSummary
Where
CH_ClientSummary.MachineID=MachineIdGroupXRef.MachineID)
And
ArchitectureKey=5
-- Update other data
Update
CH_ClientSummary
set NetBiosName = ( Select TOP 1
System_DISC.Netbios_Name0 from System_DISC
Where
CH_ClientSummary.MachineID = System_DISC.ItemKey ),
SiteCode = (
Select TOP 1 Sites_DATA.SiteCode from Sites_DATA
Where CH_ClientSummary.MachineID = Sites_DATA.MachineID ),
Version
= ( Select TOP 1 System_DISC.Client_Version0 from System_DISC
Where CH_ClientSummary.MachineID = System_DISC.ItemKey
),
LastDDR = DateAdd( mi, isnull(-wsd.TimezoneOffset, @UTCdiff), (
Select TOP 1 DiscItemAgents.AgentTime from DiscItemAgents
inner join Agents as ag on DiscItemAgents.AgentID = ag.AgentID
Where CH_ClientSummary.MachineID = DiscItemAgents.ItemKey and
ag.AgentName = 'Heartbeat Discovery' )
),
LastHW = DateAdd( mi, -wsd.TimezoneOffset, wsd.LastHWScan),
LastSW = DateAdd( mi, isnull(-wsd.TimezoneOffset, @UTCdiff), (
Select TOP 1 SoftwareInventoryStatus.LastUpdateDate from SoftwareInventoryStatus
Where
CH_ClientSummary.MachineID = SoftwareInventoryStatus.ClientId )
),
LastStatusMessage = ( Select
max(sm.Time) from StatusMessages as sm
inner
join StatusMessageAttributes as sma on
sma.RecordID = sm.RecordID
inner join
MachineIdGroupXRef as mgx on
sma.AttributeValue = mgx.GUID
where
sma.AttributeID=408 and
CH_ClientSummary.MachineID=mgx.MachineID ),
Obsolete = (Select TOP
1 System_DISC.Obsolete0 from System_DISC
where
CH_ClientSummary.MachineID = System_DISC.ItemKey )
from
CH_ClientSummary
left outer join WorkstationStatus_DATA wsd on
CH_ClientSummary.MachineID = wsd.MachineID
-- Update with Client
Deployment FSP data
Update CH_ClientSummary
set
ClientFrameworkHealthy = h.IsHealthy,
ClientDeployed = (Case When
h.det is null then 0
Else 1
END),
ClientAssigned = (Case
When h.aet is null then 0
Else 1
END)
FROM CH_ClientSummary cs
inner join MachineIdGroupXRef mgx on cs.MachineID = mgx.MachineID
inner join
(
Select cds.SMSID,
MIN(Case when chs.HealthState > 1 then 0
else 1 end) as IsHealthy,
MAX(cds.DeploymentEndTime) as det,
MAX(cds.AssignmentEndTime) as aet
From ClientDeploymentState as cds
left outer join ClientHealthState as chs on
cds.RecordID = chs.RecordID
Group By cds.SMSID
) as h on
mgx.GUID = h.SMSID
END
/******************CODE ABOVE HERE********************************/
Questions or comments? Please contact me!