January 23, 2015 at 3:19 pm
DTML (1/23/2015)
Perhaps you or someone else can clarify the following for me? Using the 2012 AdventureWorks database:a) I ran the CREATE TABLE script as it is in the QoD and the is_ansi_null_default_on was set to 0 (off)
The table was created, and the myid was created as Not Null and as a PK .
The INSERT statements failed with Msg 515...column does not allow nulls...etc.
Both SELECT statements return 0.
All of this makes sense - except the primary key. Either you uncovered a bug (frankly put, quite unlikely), or you made a mistake somewhere.
Can you tell me exactly how you verified that the column was created as a primary key? I tried to reproduce what you saw, but I did not see any indication of the column being a primary key.
January 25, 2015 at 1:23 pm
Hugo,
I don't believe I can upload any files here to show you screen shots can I? So, I will describe exactly what I did.
When I got the initial error about the INSERT statement failing, I looked in the Object Explorer of SSMS, found the dbo.MYTABLE, expanded its columns and there is a key-symbol next to the MYID column representing that it is a primary key, and in brackets next to it is "(PK, int, not null)."
I initially ran the QoD script, by simply cutting and pasting it exactly as it was into SSMS, after I opened a *New Query* window and ensured that the context was my "AdventureWorks2012" database (below is the content of everything I have done thus far). As stated I modified, the ANSI_NULL_DEFAULT option (you will see that below too), re-ran the QoD script with a newly named table...etc. The newly named table (i.e. mytableWithAnsiNullDfltOn), was created and did not have the key-symbol or (PK, not null) values next to its columns in the Object Explorer.
NOTE: I have refreshed the Object Explorer as well, and the dbo.MYTABLE still shows up as PK.
Ahhhhhhh Haaaaaaaa! Wait a second, as I am documenting this I just realized that MYTABLE table is showing up in uppercase in the Object Explorer (yet the script from the QoD was in lowercase), and the renamed table I created (i.e. mytableWithAnsiNullDfltOn) is showing up in lowercase...OUCH! I must have created a MYTABLE at some other time and not noticed this! Sure enough, when I checked the create_date column (i.e. select * from sys.tables order by 1) MYTABLE had been created some 3 weeks ago!
My apologies everyone. So, I dropped the MYTABLE, ran the following ALTER DATABASE AdventureWorks2012 SET ANSI_NULL_DEFAULT OFF; (to get the database back to the state it was before I was caught by this). Then started all over with the same script pasted below!! This time however, the INSERT statements worked fine with the *mytable*. The myid column was not a primary key and it was nullable.
My apologies Hugo - it appears the ANSI_NULL_DEFAULT was not at all the problem - it was the fact that I had a previous MYTABLE in my system which I created as having myid as a PK!!
Thanks everyone for having the patience and taking the time to help me work my way through this!! 🙂
--Script from the QoD:
--NOTE - Evidently I missed seeing the following error:
-- "There is already an object named 'mytable' in the database." when I ran this the first time!!
--Thus causing all the confusion!!
CREATE TABLE mytable ( myid INT, mychar VARCHAR(10) );
GO
--When I run the following INSERT statement I get:
-- Msg 515, Level 16, State 2, Line 1
-- Cannot insert the value NULL into column 'MYID', table 'AdventureWorks2012.dbo.MYTABLE';
-- column does not allow nulls. INSERT fails.
-- The statement has been terminated.
INSERT mytable
( myid, mychar )
VALUES
( 1, 'A' ),
( 2 , 'B'),
( NULL, 'C' ),
( 4, 'D' ),
( NULL, 'E' ),
( 6, 'F' );
--Returns no records
select * from dbo.MYTABLE
--Returns 0
SELECT
COUNT(MyID)
FROM
mytable;
--Returns 0
SELECT
COUNT( DISTINCT MyID)
FROM
mytable;
--Never ran this:
--DROP TABLE mytable;
--Question of Day reply suggested that I had
-- "My guess is that the database where DTML ran the script
-- has ANSI_NULL_DEFAULT off, thus making columns NOT NULL by default"
--So I googled the following: https://msdn.microsoft.com/en-us/library/bb522682.aspx
-- "The status of this option can be determined by examining the
-- is_ansi_null_default_on column in the sys.databases catalog view
-- or the IsAnsiNullDefault property of the DATABASEPROPERTYEX function."
--Returns 0 (meaning it is off)
SELECT SD.is_ansi_null_default_on FROM sys.databases SD WHERE SD.name = 'AdventureWorks2012'
--Execute the following to change the value to on as per
--this website: https://msdn.microsoft.com/en-us/library/ms187375.aspx
ALTER DATABASE AdventureWorks2012 SET ANSI_NULL_DEFAULT ON;
--Now returns 1 (meaning it is on)...so should make columns NULL by default.
SELECT SD.is_ansi_null_default_on FROM sys.databases SD WHERE SD.name = 'AdventureWorks2012'
--Re-run the QoD script but changed the table name
CREATE TABLE mytableWithAnsiNullDfltOn ( myid INT, mychar VARCHAR(10) );
GO
--Runs without failure (6 row(s) affected)
INSERT mytableWithAnsiNullDfltOn
( myid, mychar )
VALUES
( 1, 'A' ),
( 2 , 'B'),
( NULL, 'C' ),
( 4, 'D' ),
( NULL, 'E' ),
( 6, 'F' );
--6 rows returned
select * from dbo.mytableWithAnsiNullDfltOn
--returns 4 rows (i.e. doesn't count the nulls)
SELECT
COUNT(MyID)
FROM
mytableWithAnsiNullDfltOn;
--returns 4 rows (i.e. doesn't count the nulls)
SELECT
COUNT( DISTINCT MyID)
FROM
mytableWithAnsiNullDfltOn;
January 25, 2015 at 1:54 pm
DTML (1/25/2015)
My apologies Hugo - it appears the ANSI_NULL_DEFAULT was not at all the problem - it was the fact that I had a previous MYTABLE in my system which I created as having myid as a PK!!
Thanks for getting back to this, DTML! I was convinced that there had to be some logical explanation for what you witnessed. I am very glad that you found this explanation, and shared it with us.
Well done! 😉
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply