set ansi_nulls and arithabort - 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)

set ansi_nulls and arithabort

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-03-26 : 11:22:07

When I run a sp, I recieve an message from SQL server that my set ANSI_NULLS and ARITHABORT values are not correct.

I stepped though the sp and ran parts of it individually in Query analyzer and it all ran sucessfully. Just wondering where the problem is.

Why does it run sucessfully outside the sp, but fails in the sp.

When I add the ANSI_NULL tot he sp, I lose the code in my sp that follows the SET ANSI_NULL statement. Its weird.

Has anyone experienced this before.
Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-26 : 11:35:18

Can you post your SP here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-03-26 : 13:43:26

The following is the sp
ALTER PROCEDURE [DBO].[MIS_PROOF_REPORTS] AS

-- INSERT INTO #NOTTOPROOF FROM RAW_PROOF_OT_TO_PROOF_VW

Select
VERSION,filekey,Source_Sys_CD,unit_coll,regimental,tel,descript,TOTAL,daterecvd,Q1a,Q2a,Q3a,Q4a,Q5a,Q6a,Q7a,Q8a,Q9a,' ' as Q10,Q10a,Q11a,Q12a,Q13a,Q14a,Q15a,Q16a,Q17a,Q18a
into #Nottoproof
from RAWPROOF_NOT_TO_PROOF_VW

--UPDATE RAWPROOF = RAWPROOF_NOT_TO_PROOF_VW IF FILES EXIST IN RAWPROOF_NOT_TO_PROOF_VW

update A
Set
A.VERSION=B.VERSION,
A.UNIT_COLL=B.UNIT_COLL,
A.regimental=B.regimental,
A.tel=B.tel,
A.descript=B.descript,
A.TOTAL=B.TOTAL,
A.daterecvd=B.daterecvd,
A.Q1a=B.Q1a,
A.Q2a=B.Q2a,
A.Q3a=B.Q3a,
A.Q4a=B.Q4a,
A.Q5a=B.Q5a,
A.Q6a=B.Q6a,
A.Q7a=B.Q7a,
A.Q8a=B.Q8a,
A.Q9a=B.Q9a,
A.Q10=B.Q10,
A.Q10a=B.Q10a,
A.Q11a=B.Q11a,
A.Q12a=B.Q12a,
A.Q13a=B.Q13a,
A.Q14a=B.Q14a,
A.Q15a=B.Q15a,
A.Q16a=B.Q16a,
A.Q17a=B.Q17a,
A.Q18a=B.Q18a
FROM #Nottoproof B JOIN RAWPROOF A ON A.FILEKEY=B.FILEKEY AND A.SOURCE_SYS_CD=B.SOURCE_SYS_CD
WHERE EXISTS
(SELECT * FROM #Nottoproof c WHERE C.FILEKEY=a.FILEKEY AND C.SOURCE_SYS_CD=a.SOURCE_SYS_CD)

--APPEND RAWPROOF_NOT_TO_PROOF TO RAWPROOF WHERE FILES THAT DON'T ALREADY EXISTS IN RAWPROOF
INSERT RAWPROOF
SELECT * FROM #Nottoproof A WHERE NOT EXISTS
(SELECT * FROM RAWPROOF B WHERE A.FILEKEY=B.FILEKEY AND A.SOURCE_SYS_CD=B.SOURCE_SYS_CD)

-- SUMMARY TABLE OF SCORES, HOURS AND FILE DETAILS FOR PROOFED FILES
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 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

TRUNCATE TABLE PROOFALL
INSERT PROOFALL SELECT * FROM PROOFALL_VW

TRUNCATE TABLE PROOFRPD
INSERT PROOFRPD SELECT * FROM PROOFRPD_VW

TRUNCATE TABLE PROOFPID
INSERT PROOFPID SELECT * FROM PROOFPID_VW

TRUNCATE TABLE PROOFOFD
INSERT PROOFOFD SELECT * FROM PROOFOFD_VW

TRUNCATE TABLE PROOFHD
INSERT PROOFHD SELECT * FROM PROOFHD_VW

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-03-27 : 15:33:22

The reason the code was blanking out after the
set ansi_nulls OFF was I had GO after it.

I put the SET ANSI_NULLS OFF and SET ARITHABORT ON at the begining of the sp and it seems to execute fine. There was an embeded sp so I needed to se those values there aswell.

I did more research and found that I need these values set because there is a clustered index on one of the updating tables (PIRS) that included a computed field.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources