ARITHABORT AND STORED PROCS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ARITHABORT AND STORED PROCS

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-04-19 : 10:24:02

I have a stored proc I am calling from a DTS package. The package has the following command.
exec mis_reports

when I run the package it fails with the following error 'incorrect set value for Arithabort'.When I execute the sp from Query analyzer. I completed without error. I can't understand why?

The code in the sp is as follows:
ALTER PROCEDURE [DBO].[MIS_REPORTS] AS

SET ARITHABORT ON
SET ANSI_NULLS OFF

TRUNCATE TABLE PRF_SUMMARY

EXEC PRF_SUMMARY_SP

DELETE FROM PRF_SUMMARY
WHERE ALT_FILE_CLTR_CD LIKE 'N%'

UPDATE A
SET A.PROOF_TOTAL=B.SCORE
FROM PIRS A JOIN PRF_SUMMARY B ON A.SOURCE_SYS_CD=B.SOURCE_SYS_CD AND A.UNIT_COLL=B.ALT_FILE_CLTR_CD AND A.FILE_YEAR=B.FILE_YEAR AND A.FILE_OCC_NUM=B.FILE_OCCURENCE_NO
WHERE A.SOURCE_SYS_CD=B.SOURCE_SYS_CD AND A.UNIT_COLL=B.ALT_FILE_CLTR_CD AND A.FILE_YEAR=B.FILE_YEAR AND A.FILE_OCC_NUM=B.FILE_OCCURENCE_NO

--REPORTS

TRUNCATE TABLE PRF_TARGET_RPT
INSERT PRF_TARGET_RPT SELECT * FROM PRF_TARGET_VW ORDER BY 1

TRUNCATE TABLE PROOF80D
INSERT PROOF80D SELECT * FROM PROOF80D_VW

TRUNCATE TABLE PROOFFD
INSERT PROOFFD SELECT * FROM PROOFFD_VW

TRUNCATE TABLE PRUNIT
INSERT PRUNIT SELECT * FROM PRUNIT_VW

/*THIS WAS REPLACED WITH PRF_TARGET_RPT

TRUNCATE TABLE PROOFALL
INSERT PROOFALL SELECT * FROM PROOFALL_VW
*/

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The code contained in prf_summary_sp is as follows:
ALTER PROCEDURE [DBO].[PRF_SUMMARY_SP] AS

/*****************************************************************************
STEP 1:
GET THE LATEST INSTANCE OF EACH PROOFED FILE FROM THE PROOF SYSTEM
******************************************************************************/
SET ARITHABORT ON
SET ANSI_NULLS OFF

INSERT DBO.PRF_SUMMARY
SELECT B.SCORE_DT,
NULL,
B.SOURCE_SYS_CD,
B.CLTR_CD,
B.FILE_YEAR,
B.FILE_OCCURENCE_NO,
NULL,
NULL,
B.ALT_FILE_CLTR_CD,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

FROM DBO.PRF_OPERATIONAL_FILE B
INNER JOIN
(
SELECT MAX(A.SCORE_DT) AS SCORE_DT, A.SOURCE_SYS_CD,A.CLTR_CD,A.FILE_YEAR,A.FILE_OCCURENCE_NO
FROM DBO.PRF_OPERATIONAL_FILE A
GROUP BY A.SOURCE_SYS_CD,A.CLTR_CD,A.FILE_YEAR,A.FILE_OCCURENCE_NO

) C ON B.SCORE_DT = C.SCORE_DT AND B.SOURCE_SYS_CD = C.SOURCE_SYS_CD AND B.CLTR_CD = C.CLTR_CD AND B.FILE_YEAR = C.FILE_YEAR AND B.FILE_OCCURENCE_NO = C.FILE_OCCURENCE_NO

/*****************************************************************************
STEP 2:
GET THE PROGRAM AND VERSION FOR THE LATEST INSTANCE OF EACH PROOFED FILE.

******************************************************************************/

UPDATE DBO.PRF_SUMMARY
SET PROGRAM_CD = B.PROGRAM_CD,
PROOF_VERSION = B.QUES_VER
FROM DBO.PRF_SUMMARY A
LEFT OUTER JOIN (
SELECT MAX(PR.QUES_VER) AS QUES_VER,
MAX(PR.PROGRAM_CD) AS PROGRAM_CD,
PR.SCORE_DT,PR.SOURCE_SYS_CD,PR.CLTR_CD,PR.FILE_YEAR,PR.FILE_OCCURENCE_NO,Question_id, Answer_cd
FROM DBO.PRF_RESULT PR
GROUP BY PR.SCORE_DT,PR.SOURCE_SYS_CD,PR.CLTR_CD,PR.FILE_YEAR,PR.FILE_OCCURENCE_NO,Question_id, Answer_cd
) B ON A.SCORE_DT = B.SCORE_DT AND A.SOURCE_SYS_CD = B.SOURCE_SYS_CD AND A.CLTR_CD = B.CLTR_CD AND A.FILE_YEAR = B.FILE_YEAR AND A.FILE_OCCURENCE_NO = B.FILE_OCCURENCE_NO

/*****************************************************************************
STEP 3:
GET THE SCORE FOR THE LATEST INSTANCE OF EACH PROOFED FILE.
******************************************************************************/
-- FOR HISTORICAL DATA

UPDATE DBO.PRF_SUMMARY
SET SCORE = B.ANSWER_CD
FROM DBO.PRF_SUMMARY A
LEFT OUTER JOIN (
SELECT ANSWER_CD,PR.SCORE_DT,PR.SOURCE_SYS_CD,PR.CLTR_CD,PR.FILE_YEAR,PR.FILE_OCCURENCE_NO
FROM DBO.PRF_RESULT PR
WHERE QUESTION_ID = 1000
) B ON A.SCORE_DT = B.SCORE_DT AND A.SOURCE_SYS_CD = B.SOURCE_SYS_CD AND A.CLTR_CD = B.CLTR_CD AND A.FILE_YEAR = B.FILE_YEAR AND A.FILE_OCCURENCE_NO = B.FILE_OCCURENCE_NO
WHERE PROOF_VERSION = 0

--FOR CURRENT DATA
UPDATE DBO.PRF_SUMMARY
SET SCORE = B.SCORE
FROM DBO.PRF_SUMMARY A
LEFT OUTER JOIN (
SELECT SUM(PA.SCORE) AS SCORE,
PR.SCORE_DT,PR.SOURCE_SYS_CD,PR.CLTR_CD,PR.FILE_YEAR,PR.FILE_OCCURENCE_NO
FROM DBO.PRF_RESULT PR
INNER JOIN PRF_ANSWER PA ON PA.QUES_VER = PR.QUES_VER AND PA.QUESTION_ID = PR.QUESTION_ID AND PA.ANSWER_CD = PR.ANSWER_CD AND PA.PROGRAM_CD = PR.PROGRAM_CD
GROUP BY PR.SCORE_DT,PR.SOURCE_SYS_CD,PR.CLTR_CD,PR.FILE_YEAR,PR.FILE_OCCURENCE_NO
) B ON A.SCORE_DT = B.SCORE_DT AND A.SOURCE_SYS_CD = B.SOURCE_SYS_CD AND A.CLTR_CD = B.CLTR_CD AND A.FILE_YEAR = B.FILE_YEAR AND A.FILE_OCCURENCE_NO = B.FILE_OCCURENCE_NO
WHERE PROOF_VERSION > 0

/*****************************************************************************
STEP 3.5:
DELETE FROM PRF_SUMMARY FILES THAT SHOULD NOT BE PROOFED AND HAD BEEN
******************************************************************************/

--CREATE #PRF_NOT_TO_PROOF
SELECT SOURCE_SYS_CD,DET_COLL,FILE_YEAR,FILE_OCC_NUM, P.UNIT_COLL
INTO #PRF_NOT_TO_PROOF
FROM dbo.pirs P
WHERE NOT EXISTS
(
SELECT *
FROM(
SELECT NULL AS SCORE_DT, PROGRAM_CD, SOURCE_SYS_CD, CLTR_CD, FILE_YEAR, FILE_OCCURENCE_NO, NULL AS PROOF_VERSION, 0 AS SCORE, ALT_FILE_CLTR_CD FROM PRF_PIRS_OSR_FILE_CD
UNION ALL
SELECT NULL AS SCORE_DT, PROGRAM_CD, SOURCE_SYS_CD, CLTR_CD, FILE_YEAR, FILE_OCCURENCE_NO, NULL AS PROOF_VERSION, 0 AS SCORE, ALT_FILE_CLTR_CD FROM PRF_PROS_FILE_CD
UNION ALL
SELECT NULL AS SCORE_DT, PROGRAM_CD, SOURCE_SYS_CD, CLTR_CD, FILE_YEAR, FILE_OCCURENCE_NO, NULL AS PROOF_VERSION, 0 AS SCORE, ALT_FILE_CLTR_CD FROM PRF_BC_PRIME_FILE_CD
UNION ALL
SELECT NULL AS SCORE_DT, PROGRAM_CD, SOURCE_SYS_CD, CLTR_CD, FILE_YEAR, FILE_OCCURENCE_NO, NULL AS PROOF_VERSION, 0 AS SCORE, ALT_FILE_CLTR_CD FROM PRF_HALIFAX_FILE_CD
) TEMP
WHERE TEMP.SOURCE_SYS_CD = P.SOURCE_SYS_CD AND TEMP.CLTR_CD = P.DET_COLL AND TEMP.FILE_YEAR = P.FILE_YEAR AND TEMP.FILE_OCCURENCE_NO = P.FILE_OCC_NUM AND TEMP.ALT_FILE_CLTR_CD=P.UNIT_COLL
)

--DELETE FROM PRF_SUMMARY WHERE EXISTS IN PRF_NOT_TO_PROOF
DELETE FROM prf_summary
WHERE EXISTS
(SELECT * FROM #PRF_NOT_TO_PROOF B
WHERE prf_summary.SOURCE_SYS_CD=b.Source_Sys_CD and prf_summary.CLTR_CD=b.det_coll and prf_summary.FILE_YEAR=b.file_year and prf_summary.FILE_OCCURENCE_NO=b.file_occ_num and prf_summary.ALT_FILE_CLTR_CD=b.unit_coll)

/*****************************************************************************
STEP 4:
ADD FILES THAT SHOULD BE PROOFED, BUT HAVEN'T BEEN.
******************************************************************************/

INSERT DBO.PRF_SUMMARY
SELECT *,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM (
SELECT NULL AS SCORE_DT, PROGRAM_CD, SOURCE_SYS_CD, CLTR_CD, FILE_YEAR, FILE_OCCURENCE_NO, NULL AS PROOF_VERSION, 0 AS SCORE, ALT_FILE_CLTR_CD FROM PRF_PIRS_OSR_FILE_CD
UNION ALL
SELECT NULL AS SCORE_DT, PROGRAM_CD, SOURCE_SYS_CD, CLTR_CD, FILE_YEAR, FILE_OCCURENCE_NO, NULL AS PROOF_VERSION, 0 AS SCORE, ALT_FILE_CLTR_CD FROM PRF_PROS_FILE_CD
UNION ALL
SELECT NULL AS SCORE_DT, PROGRAM_CD, SOURCE_SYS_CD, CLTR_CD, FILE_YEAR, FILE_OCCURENCE_NO, NULL AS PROOF_VERSION, 0 AS SCORE, ALT_FILE_CLTR_CD FROM PRF_BC_PRIME_FILE_CD
UNION ALL
SELECT NULL AS SCORE_DT, PROGRAM_CD, SOURCE_SYS_CD, CLTR_CD, FILE_YEAR, FILE_OCCURENCE_NO, NULL AS PROOF_VERSION, 0 AS SCORE, ALT_FILE_CLTR_CD FROM PRF_HALIFAX_FILE_CD
) TEMP
WHERE NOT EXISTS (SELECT * FROM PRF_SUMMARY P WHERE TEMP.SOURCE_SYS_CD = P.SOURCE_SYS_CD AND TEMP.CLTR_CD = P.CLTR_CD AND TEMP.FILE_YEAR = P.FILE_YEAR AND TEMP.FILE_OCCURENCE_NO = P.FILE_OCCURENCE_NO)

/*****************************************************************************
STEP 5:
GET THE TOTAL HOURS WORKED ON FOR ALL FILES IN PRF_SUMMARY.
******************************************************************************/
UPDATE DBO.PRF_SUMMARY
SET HOURS = ISNULL(B.HOURS,0)
FROM DBO.PRF_SUMMARY A
LEFT OUTER JOIN (
SELECT ISNULL(SUM(HOURS),0) AS HOURS,K.SOURCE_SYS_CD,K.file_owner,K.FILE_YEAR,K.FILE_OCC_NUM
FROM DBO.KING K
GROUP BY K.SOURCE_SYS_CD,K.file_owner,K.FILE_YEAR,K.FILE_OCC_NUM
)B ON A.SOURCE_SYS_CD = B.SOURCE_SYS_CD AND A.alt_file_cltr_cd = B.file_owner AND A.FILE_YEAR = B.FILE_YEAR AND A.FILE_OCCURENCE_NO = B.FILE_OCC_NUM

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-19 : 12:07:30

Those shoudld be set outside the proc.

SET ARITHABORT ON
Go
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [DBO].[MIS_REPORTS] AS
...

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-04-19 : 15:07:07

THANKS THAT WORKED!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources