Solved

ANSI NULL incorrently set, but where? & why?

Posted on 2003-11-07
14
855 Views
Last Modified: 2008-03-10
I've added a stored procedure into an existing database which is trying to delete records in a table based on where clause criteria. When I try to execute it I get a 'DELETE failed due to setting ANSI_NULL incorrectly set' error.

I've looked at the help & it appears that I can't explicitly set this option in my SP - fine, I didn't want to mess with it anyway. I've checked the option on the database connection & have set it both on & off - stopping & restarting the server after each. To no avail though, the sql query analyzer debug gives me the same error.

I have noticed that if I open the SP in the sql query analyzer for edit, the text of my SP is changed so that a 'SET ANSI_NULL OFF' & 'GO' are added before my 'CREATE PROCEDURE'. So I've summised that at some level between the database & the SP this option is being set. But where?

Lastly, the table in question has several simple views over it. These & the table have an index column as their primary key.

Thanks
0
Comment
Question by:SimonWray
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9700232
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 !
0
 

Author Comment

by:SimonWray
ID: 9700324
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
-----------
0
 
LVL 8

Expert Comment

by:dishanf
ID: 9700387
try to do it with error handlling
http://www.algonet.se/~sommar/error-handling-II.html
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9700397
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
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9700609
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
0
 

Author Comment

by:SimonWray
ID: 9700629
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.
0
 

Author Comment

by:SimonWray
ID: 9700781
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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 6

Expert Comment

by:ChrisKing
ID: 9700834
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9701358
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.
"
0
 

Author Comment

by:SimonWray
ID: 9702214
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
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9702323
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.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9702351
What is this property?

SET ANSI_NULL_DFLT_ON {ON | OFF}

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 9702384
To make sure all the settings are correct, try this from QA (not EM, it's too flaky):

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
GO
CREATE PROCEDURE ...
...


All of those settings are required to make indexes on computed columns or indexed views work properly.
0
 

Author Comment

by:SimonWray
ID: 9702422
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now