When I run this stored proc in QA no errors fire. But when the job completes, I get:
"Stored Procedure: TWO.dbo.aSph_IMSworkUIClient
Return Code = -4
Query batch completed with errors."
Can anyone tell me what this means? How do I fix it so it completes without errors?
Pri Keys: RM00101 - CUSTNMBR
RM00102 - CUSTNMBR, ADRSCODE
"Stored Procedure: TWO.dbo.aSph_IMSworkUIClient
Return Code = -4
Query batch completed with errors."
Can anyone tell me what this means? How do I fix it so it completes without errors?
Code:
BEGIN
--Declare cursor
DECLARE WorkCursor CURSOR SCROLL DYNAMIC FOR
SELECT ISNULL(NO_CLIENT, '0'), ISNULL(NOM_CLIENT,''), ISNULL(ADRESSE,''), ISNULL(VILLE,''), ISNULL(PROVINCE,''), ISNULL(PAYS,''), ISNULL(CODE_POSTAL,''),
ISNULL(TEL,''), ISNULL(FAX,''), ISNULL(CREATION,''), ISNULL(CONTACT,''), ISNULL(TEL_CONTACT,'')
FROM aSph_IMSworkClient
OPEN WorkCursor
--Fill cursor with new recordset
FETCH NEXT FROM WorkCursor
INTO @CUSTNMBR, @CUSTNAME, @ADDRESS1, @CITY, @STATE, @COUNTRY, @ZIP, @PHONE1, @FAX, @FRSTINDT, @CNTCPRSN, @PHONE2
--While recordset exists
WHILE @@Fetch_status = 0
BEGIN
SET @recordexists = ''
-- Find out if the current customer number exists in the dynamics table.
SELECT @recordexists = CUSTNMBR
FROM RM00101
WHERE CUSTNMBR = @CUSTNMBR
IF @recordexists <> ''
BEGIN
--Where customer numbers match, update the current values in the row of the dynamics table RM00101
UPDATE RM00101
SET CUSTNMBR=@CUSTNMBR, CUSTNAME=@CUSTNAME, ADRSCODE=@ADRSCODE, ADDRESS1=@ADDRESS1, CITY=@CITY, STATE=@STATE,
COUNTRY=@COUNTRY, ZIP=@ZIP, PHONE1=@PHONE1, FAX=@FAX, FRSTINDT=@FRSTINDT, CNTCPRSN=@CNTCPRSN, PHONE2=@PHONE2
WHERE CUSTNMBR = @CUSTNMBR
END
ELSE
BEGIN
--Add a new row if customer number doesn't already exist insert new values in the dynamics table RM00101
INSERT INTO RM00101 (CUSTNMBR, CUSTNAME, ADRSCODE, ADDRESS1, CITY, STATE, COUNTRY, ZIP, PHONE1, FAX, FRSTINDT, CNTCPRSN, PHONE2)
VALUES (@CUSTNMBR, @CUSTNAME, @ADRSCODE, @ADDRESS1, @CITY, @STATE, @COUNTRY, @ZIP, @PHONE1, @FAX, @FRSTINDT, @CNTCPRSN,
@PHONE2)
SET @ErrorReturn = @@ERROR
END
SET @recordexists2 = ''
-- Find out if the current customer number exists in the dynamics table.
SELECT @recordexists2 = CUSTNMBR
FROM RM00102
WHERE CUSTNMBR = @CUSTNMBR
AND ADRSCODE = 'PRINCIPALE' OR ADRSCODE = '' OR ADRSCODE IS NULL
IF @recordexists2 <> ''
BEGIN
--Where customer numbers match, update the current values in the row of the dynamics table RM00102
UPDATE RM00102
SET CUSTNMBR=@CUSTNMBR, ADRSCODE=@ADRSCODE, ADDRESS1=@ADDRESS1, CITY=@CITY, STATE=@STATE,
COUNTRY=@COUNTRY, ZIP=@ZIP, PHONE1=@PHONE1, FAX=@FAX, PHONE2=@PHONE2
WHERE CUSTNMBR = @CUSTNMBR
END
ELSE
BEGIN
--Add a new row if customer number doesn't already exist insert new values in the dynamics table RM00102
INSERT INTO RM00102 (CUSTNMBR, ADRSCODE, ADDRESS1, CITY, STATE, COUNTRY, ZIP, PHONE1, FAX, PHONE2)
VALUES (@CUSTNMBR, @ADRSCODE, @ADDRESS1, @CITY, @STATE, @COUNTRY, @ZIP, @PHONE1, @FAX, @PHONE2)
SET @ErrorReturn = @@ERROR
END
--Fill cursor with new recordset
FETCH NEXT FROM WorkCursor
INTO @CUSTNMBR, @CUSTNAME, @ADDRESS1, @CITY, @STATE, @COUNTRY, @ZIP, @PHONE1, @FAX, @FRSTINDT, @CNTCPRSN, @PHONE2
END
CLOSE WorkCursor
DEALLOCATE WorkCursor
END
GO
Pri Keys: RM00101 - CUSTNMBR
RM00102 - CUSTNMBR, ADRSCODE