need Help for failed JOB

Question

Post reply

need Help for failed JOB

Leopard

Ten Centuries

Points: 1084
More actions
March 7, 2017 at 9:52 pm

#322436

Hi All,
I had problem for job schedule in my SQL anyone can help for step by step solving this issue or what can i do..??
Message error :
Executedas user: NT AUTHORITY\NETWORK SERVICE. The MERGE statement attempted to UPDATEor DELETE the same row more than once. This happens when a target row matchesmore than one source row. A MERGE statement cannot UPDATE/DELETE the same rowof the target table multiple times. Refine the ON clause to ensure a target rowmatches at most one source row, or use the GROUP BY clause to group the sourcerows. [SQLSTATE 42000] (Error 8672). The step failed.
Thank you,
GNA.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Leopard Ten Centuries Points: 1084 More actions · Answer 1

For JOB :

JKISQLBPW005

PIP2.InitialTransactionUNSPONSOR

JKISVGENOCSDB

Aggregate LeadsActivity & CustBackend

Thank you.
GNA

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Leopard - Tuesday, March 7, 2017 9:54 PM
For JOB :

JKISQLBPW005
PIP2.InitialTransactionUNSPONSOR

JKISVGENOCSDB
Aggregate LeadsActivity & CustBackend

Thank you.
GNA

Make sure you are current on service packs for SQL Server 2008 since there was a bug with merge that was fixed in one of the earlier SPs.
Other than that, It's not really the job but it's the SQL statement and how you are doing the merge. Without seeing the sql and DDL statements, you have duplicate rows matching based on the join. So your target row matches more than one source row. You need to check for duplicate rows as well as make sure the join only matches one row.

Sue

Leopard Ten Centuries Points: 1084 More actions · Answer 3

Sue_H - Wednesday, March 8, 2017 6:52 AM
Leopard - Tuesday, March 7, 2017 9:54 PM
For JOB :

JKISQLBPW005
PIP2.InitialTransactionUNSPONSOR

JKISVGENOCSDB
Aggregate LeadsActivity & CustBackend

Thank you.
GNA
Make sure you are current on service packs for SQL Server 2008 since there was a bug with merge that was fixed in one of the earlier SPs.
Other than that, It's not really the job but it's the SQL statement and how you are doing the merge. Without seeing the sql and DDL statements, you have duplicate rows matching based on the join. So your target row matches more than one source row. You need to check for duplicate rows as well as make sure the join only matches one row.
Sue

Hi Sue,

Thank you for enlightenment and explanation.

if this script has an error?

USE [genesys_datamart]
GO

/****** Object: StoredProcedure [dbo].[Rx_LeadActivity_DAY] Script Date: 3/8/2017 2:56:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Rx_LeadActivity_DAY]
(
@timeKey varchar(8)
)
AS

SET NOCOUNT ON

-- Delete data that we are going to aggregate so that it is safe to re-run the Stored Procedure
DELETE FROM T_REPORT_LeadActivity
WHERE Citas_Campaign_Id IN (
       SELECT DISTINCT CampaignId FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
       WHERE StampIn BETWEEN CAST(@timeKey AS DATETIME) AND CAST(@timeKey AS DATETIME)+1
)
AND Server IN (
       SELECT DISTINCT Server FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
       WHERE StampIn BETWEEN CAST(@timeKey AS DATETIME) AND CAST(@timeKey AS DATETIME)+1
)
;

DELETE FROM [genesys_datamart].[dbo].[T_REPORT_LeadActivity_DAY]
WHERE Citas_Campaign_Id IN (
       SELECT DISTINCT CampaignId FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
       WHERE StampIn BETWEEN CAST(@timeKey AS DATETIME) AND CAST(@timeKey AS DATETIME)+1
)
AND Server IN (
       SELECT DISTINCT Server FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
       WHERE StampIn BETWEEN CAST(@timeKey AS DATETIME) AND CAST(@timeKey AS DATETIME)+1
)
;

-- Aggregate data for single days
INSERT INTO [genesys_datamart].[dbo].[T_REPORT_LeadActivity_DAY]
(CallDate, DateTimeKey, Citas_Campaign_Id, SERVER, ProductId, ProspectId, No_Of_Attempts, DBase, Answered, Contact, Interest, Followup, NotInterest, PIF, ANP)
SELECT
       calldate,
       CONVERT(VARCHAR(20), calldate, 112) AS datetimekey,
       CampaignId, CallCenterId, ProductId, ProspectId,
       No_Of_Attempts,
       COUNT(ProspectId) AS DBase,
       SUM(Answered) AS Answered,
       SUM(Contact) AS Contact,
       SUM(Interest) AS Interest,
       SUM(FollowUp) AS FollowUp,
       SUM(NotInterest) AS NotInterest,
       SUM(PIF) AS PIF,
       SUM(ANP) AS ANP
FROM
(
SELECT
       DATEADD(dd, 0, DATEDIFF(dd, 0, StampIn)) AS calldate,
       CampaignId, CallCenterId, ProspectId,
       MultiProduct AS ProductId,
       COUNT(CallCenterId) AS NO_OF_Attempts,
       SUM(CASE WHEN cstat.AnswerStatus='Y' THEN 1 ELSE 0 END) AS Answered,
       SUM(CASE WHEN cstat.ContactStatus='Y' THEN 1 ELSE 0 END) - SUM(CASE WHEN fullcallid='203' THEN 1 ELSE 0 END) AS Contact,
       SUM(CASE WHEN cstat.InterestStatus='Y' THEN 1 ELSE 0 END) AS Interest,
       SUM(CASE WHEN cstat.FollowUpStatus='Y' THEN 1 ELSE 0 END) AS FollowUp,
       SUM(CASE WHEN cstat.NotInterestStatus='Y' THEN 1 ELSE 0 END) AS NotInterest,
       SUM(CASE WHEN ANPValue>0 THEN 1 ELSE 0 END) AS PIF,
       SUM(ANPValue) ANP
FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
LEFT JOIN genesys_datamart.dbo.T_REPORT_CallerStatus cstat ON rawd.CallerId = cstat.CallerId
WHERE CampaignId IN (
       SELECT DISTINCT CampaignId FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
       WHERE StampIn BETWEEN CAST(@timeKey AS DATETIME) AND CAST(@timeKey AS DATETIME)+1
)
AND CallCenterId IN (
       SELECT DISTINCT CallCenterId FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
       WHERE StampIn BETWEEN CAST(@timeKey AS DATETIME) AND CAST(@timeKey AS DATETIME)+1
)
GROUP BY
DATEADD(dd, 0, DATEDIFF(dd, 0, StampIn)),
CampaignId, CallCenterId, MultiProduct, ProspectId
) temp
GROUP BY calldate, CampaignId, CallCenterId, ProductId, ProspectId, NO_OF_Attempts
;

-- Aggregate data for the entire Campaign

INSERT INTO [genesys_datamart].[dbo].[T_REPORT_LeadActivity]
(Citas_Campaign_Id, SERVER, ProductId, ProspectId, No_Of_Attempts, DBase, Answered, Contact, Interest, Followup, NotInterest, PIF, ANP)
SELECT
       Citas_Campaign_Id, Server, ProductId, ProspectId,
       SUM(No_Of_Attempts) AS No_Of_Attempts,
       COUNT(ProspectId) AS DBase,
       SUM(Answered) AS Answered,
       SUM(Contact) AS Contact,
       SUM(Interest) AS Interest,
       SUM(FollowUp) AS FollowUp,
       SUM(NotInterest) AS NotInterest,
       SUM(PIF) AS PIF,
       SUM(ANP) AS ANP
FROM genesys_datamart.dbo.T_REPORT_LeadActivity_DAY leadday
WHERE Citas_Campaign_Id IN (
       SELECT DISTINCT CampaignId FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
       WHERE StampIn BETWEEN CAST(@timeKey AS DATETIME) AND CAST(@timeKey AS DATETIME)+1
)
AND Server IN (
       SELECT DISTINCT Server FROM genesys_datamart.dbo.T_REPORT_NewRawData rawd
       WHERE StampIn BETWEEN CAST(@timeKey AS DATETIME) AND CAST(@timeKey AS DATETIME)+1
)
GROUP BY Citas_Campaign_Id, SERVER, ProductId, ProspectId
;

MERGE genesys_datamart.dbo.T_REPORT_LeadActivity_DAY AS target
USING (SELECT Citas_Campaign_Id, SERVER, ProductId, ProspectId, No_Of_Attempts FROM genesys_datamart.dbo.T_REPORT_LeadActivity) AS source
ON (
       target.Citas_Campaign_Id = source.Citas_Campaign_Id
       AND target.SERVER = source.SERVER
       AND target.ProductId = source.ProductId
       AND target.ProspectId = source.ProspectId
)
WHEN MATCHED THEN
UPDATE SET target.No_Of_Attempts = source.No_Of_Attempts
;

GO

Thank you
GNA

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Looks like when you join your source and target tables on those four columns, you have more than one value of No_Of_Attempts in your source table for one or more combinations of the four columns, and therefore SQL Server doesn't know which one to update the target with.

John

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

In other words, there may be a problem with the data where there is unexpected duplication and you need to check and make sure that's what's expected and then fix the code to allow it if it is expected. Otherwise, you need to fix the duplication.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Leopard Ten Centuries Points: 1084 More actions · Answer 6

John Mitchell-245523 - Wednesday, March 8, 2017 7:24 AM
Looks like when you join your source and target tables on those four columns, you have more than one value of No_Of_Attempts in your source table for one or more combinations of the four columns, and therefore SQL Server doesn't know which one to update the target with.
John

Hi John,

Thank you, let me try.

Leopard Ten Centuries Points: 1084 More actions · Answer 7

Jeff Moden - Wednesday, March 8, 2017 8:13 AM
In other words, there may be a problem with the data where there is unexpected duplication and you need to check and make sure that's what's expected and then fix the code to allow it if it is expected. Otherwise, you need to fix the duplication.

Hi Jeff,

Thank you.... let me check first.