ANSI NULL incorrently set, but where? & why?

Hard to tell unless you post some code, but here is some general rules

replace all instances of "!= NULL" with "IS NOT NULL"

replace all COMPARISON instances of "= NULL" with "IS NULL"

assignment instances of "= NULL" are still OK
eg
- parameter defaults (eg CREATE PROC proc (@param int = NULL, ...) AS ...)
- updates (eg UPDATE table SET col = NULL)
- variable assignments (eg SELECT @var = NULL)
- execute procedure parameters (eg EXEC proc @param = NULL)

good luck !

Here's the code: It may have bugs in it as I'm at the debug stage but have yet to get it to run!!

I'm not performing any checking on NULL or ' ' column values, unless a variable happens to be emtpy.

Thanks
Simon

----------
CREATE  PROCEDURE IRISSQL.DTASQL_UXSPXZCD AS

--- Author SAW 05.11.03
--- Post updates of ICCYREP to ICCUREP
--- Based on UXSPXZCD

--- Declare working variables
DECLARE @CURRENT_RCD_MBR VARCHAR(10)
DECLARE @LAST_RCD_MBR VARCHAR(10)

--- Declare variables for return of ICCYREL4 record
DECLARE @XXID VARCHAR(9)
DECLARE @OGCU VARCHAR(3)
DECLARE @PDNM VARCHAR(10)
DECLARE @CUSD VARCHAR(4)
DECLARE @CUDS VARCHAR(20)
DECLARE @OBXR DECIMAL(15,8)
DECLARE @OBOY VARCHAR(1)
DECLARE @CUPN INT
DECLARE @HCFG VARCHAR(1)
DECLARE @MJAY INT
DECLARE @KTAY INT
DECLARE @KDAY INT
DECLARE @IOCU VARCHAR(3)
DECLARE @SICU VARCHAR(3)
DECLARE @RCFD VARCHAR(1)

--- Cursors over the GUI single-member RoE/Ccy file & the GS Multi-member RoE/Ccy file
DECLARE C0 CURSOR FOR SELECT DISTINCT(AX_IAPDNM) FROM IRISSQL.DTASQL_ICCYREP
DECLARE C1 CURSOR FOR SELECT * FROM IRISSQL.DTASQL_ICCYREP WHERE AX_IAPDNM = @CURRENT_RCD_MBR

OPEN C0

--- Perform the first fetch
FETCH NEXT FROM C0 INTO @CURRENT_RCD_MBR

--- Remove all the records from the current member in ICCUREP
DELETE FROM IRISSQL.DTASQL_ICCUREP WHERE(AX_MEMBER = @CURRENT_RCD_MBR)

WHILE @@FETCH_STATUS  = 0
--- Record found
BEGIN
      --- Read individual records for that member name from ICCYREP
      FETCH NEXT FROM C1  INTO @XXID, @OGCU, @PDNM, @CUSD, @CUDS, @OBXR, @OBOY, @CUPN, @HCFG, @MJAY, @KTAY, @KDAY, @IOCU, @SICU, @RCFD

      WHILE @@FETCH_STATUS = 0
      BEGIN      
            ---- Insert the ICCYREP rcd into the ICCUREP table & explicitly populate the AX_MEMBER column
            INSERT INTO IRISSQL.DTASQL_ICCUREP
             (AX_MEMBER, AX_IAOGCU, AX_IACUSD, AX_IACUDS, AX_IAOBXR, AX_IAOBOY, AX_IACUPN, AX_IAHCFG, AX_IAMJAY, AX_IAKTAY, AX_IAKDAY, AX_IAIOCU, AX_IASICU, AX_IARCFD)
             VALUES(@PDNM, @OGCU, @CUSD, @CUDS, @OBXR, @OBOY, @CUPN, @HCFG, @MJAY, @KTAY, @KDAY, @IOCU, @SICU, @RCFD)

            --- Read the next record
            FETCH NEXT FROM C1  INTO @XXID, @OGCU, @PDNM, @CUSD, @CUDS, @OBXR, @OBOY, @CUPN, @HCFG, @MJAY, @KTAY, @KDAY, @IOCU, @SICU, @RCFD

      END

      FETCH NEXT FROM C0 INTO @CURRENT_RCD_MBR
END

---Close the files & deallocate the resources
CLOSE C0
DEALLOCATE C0
CLOSE C1
DEALLOCATE C1
GO
-----------

try to do it with error handlling
http://www.algonet.se/~sommar/error-handling-II.html

well I can't see any ansi null issue here but you do realiase that you could rewrite all of this in 2 statements ?
and they may get you around your problem

DELETE IRISSQL.DTASQL_ICCUREP
FROM IRISSQL.DTASQL_ICCUREP a,
WHERE EXISTS (SELECT * FROM IRISSQL.DTASQL_ICCYREP b WHERE b.AX_IAPDNM = a.AX_MEMBER)

INSERT INTO IRISSQL.DTASQL_ICCUREP
(AX_MEMBER, AX_IAOGCU, AX_IACUSD, AX_IACUDS, AX_IAOBXR, AX_IAOBOY, AX_IACUPN, AX_IAHCFG, AX_IAMJAY, AX_IAKTAY, AX_IAKDAY, AX_IAIOCU, AX_IASICU, AX_IARCFD)
SELECT *                        <=== suggest you use the full column list here
FROM IRISSQL.DTASQL_ICCYREP a,
WHERE EXISTS (SELECT * FROM IRISSQL.DTASQL_ICCYREP b WHERE b.AX_IAPDNM = a.AX_MEMBER)

if the first delete is going to delete all rows, then replace it with
TRUNCATE TABLE IRISSQL.DTASQL_ICCUREP

Select Tools/Options menu. Then select "Connection Properties" tab with query analyser. You can see a place where "ANSI_NULL" property is set.


Also,

You can set these properties inside stored proc as well if you need to change this property.

Create Procedure Proc1
(

SET ANSI_NULLS ON
SET ANSI_WARNINGS OFF

)

HTH

I've tried setting the Connection Properties tab for ANSI_NULLS & have stopped & re-started the server, but I still get the same error. This is why I think that something else exists below the level of the database that is overriding it.

Also, according to the T_SQL help text the setting of ANSI_NULL within an Stored Procedure is ignored.

I shall attempt both ChrisKing's changes & also setting off of the ANSI_WARNINGS setting.

Further devlopements:
1. Chris - I've tried this SQL from you, but in debug get the error that not only is ANSI_NULL not set correctly, but neither is QUOTED_IDENTIFIER.
-------
CREATE PROCEDURE IRISSQL.DTASQL_UXSPXZCE WITH RECOMPILE AS

DELETE IRISSQL.DTASQL_ICCUREP FROM IRISSQL.DTASQL_ICCUREP AS A
WHERE EXISTS (SELECT * FROM IRISSQL.DTASQL_ICCYREP AS B WHERE(B.AX_IAPDNM = A.AX_MEMBER))

INSERT INTO IRISSQL.DTASQL_ICCUREP
(AX_MEMBER, AX_IAOGCU, AX_IACUSD, AX_IACUDS, AX_IAOBXR, AX_IAOBOY, AX_IACUPN, AX_IAHCFG, AX_IAMJAY, AX_IAKTAY, AX_IAKDAY, AX_IAIOCU, AX_IASICU, AX_IARCFD)
SELECT AX_IAPDNM, AX_IAOGCU, AX_IACUSD, AX_IACUDS, AX_IAOBXR, AX_IAOBOY, AX_IACUPN, AX_IAHCFG, AX_IAMJAY, AX_IAKTAY, AX_IAKDAY, AX_IAIOCU, AX_IASICU, AX_IARCFD
FROM IRISSQL.DTASQL_ICCYREP AS A WHERE EXISTS (SELECT * FROM IRISSQL.DTASQL_ICCYREP AS B WHERE B.AX_IAPDNM = A.AX_MEMBER)
GO
------

2. Trying to use SET ANSI_WARNINGS OFF, GO. Results in the SP syntax being incorrect.

3. In the Database Connection tab, the ANSI_NULLS & QUOTED_IDENTIFIER are both checked.

4. I've been able to run a straight DELETE FROM IRISSQL.DTASQL.ICCUREP with no trouble. There are also other SP's in the database, over other tables, that do not results in this problem.

Further suggestions most welcome, thanks.

sounds like you should check your triggers ???????????????

Point 2, remove the GO (when in a procedure), as GO ends the procedure, hence the invalid syndax

When you create the SP, the ANSI_NULLS and another setting is captured, remembered, and restored when you run the sp.  That is, those settings will be made the same as they were at the time the SP was created.  [See quote below from BOL for more details.]

So, to address the issue, issue the SET statements necessary, then CREATE or ALTER the procedure.  For example:

SET ANSI_NULL ON  -- just an example, change to OFF if needed
SET QUOTED_IDENTIFIER OFF  -- just an example, change to ON if needed
GO
CREATE/ALTER PROCEDURE ...
...


From BOL, under topic "CREATE PROCEDURE":
"
SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered. If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure.
"

Scott, I've tried the following; set the two Settings in the Database Connection Properties to unchecked (which I assume is off), stopped & re-started the server (not sure if I need to do this, but I thought it was worth a go), created a new SP in my db and pasted in the code from the previous SP, with no mention of these Settings in it.

I've then started up the query analyzer and tried to run the SP in debug, but again get the error that the two settings are incorretly set for a DELETE.

So, I've then dropped the SP, gone back & re-checked the settings (i.e. setting them ON for the database), stopped & restarted the server, and created a new SP, pasting in the code again. Query analyzer gives me exactly the same error.

I've looked for triggers on the table and it's views, but there are none.

Help?!

I have no trouble with another SP over a different table during this - i.e. it works no matter what I amend the Settings to.

Thanks

From Enterprise Manager also this can be set.

Right Mouse click on the server and Select Properties. Select Connections Tab.
Then you can set ANSI_NULLS propeties there too.

What is this property?

SET ANSI_NULL_DFLT_ON {ON | OFF}

At present both the DFLT_ON & DFLT_OFF are unchecked.

Until I encountered this problem none of the settings were checked, which I believe is the default.