Prev: Need help with a stored procedure
Next: Not nullable field doesn't get set to default on virtual server
From: Nancy on 29 Mar 2010 14:16
My database has just been moved from a regular server running SQL Server 2005 to a virtual server running SQL server 2005. I have been getting calls today because integer fields that are set to NOT allow nulls and have a default value of 0 --- defined in the table as ((0)) -- are not being set to the default value when writing a new record where the not nullable integer field has NOT been specified. Is there something that must be set up on this virtual server to trigger the use of the default value? I am simply not specifying this field in the insert and expecting it to default to 0 which is the default value. I get the 'trying to insert null into not nullable field' error message. Anyone got any wisdom to offer?



Gert-Jan Strik wrote:

Frank,First of all, SQL Server would throw an error if you explicitely inserta
21-Feb-08

Frank,

First of all, SQL Server would throw an error if you explicitely insert
a NULL value into a column that is not nullable, although it would be a
different error than the one you mention.

However, omitting the column when inserting, or inserting DEFAULT will
*not* lead to an error, but will simply insert the default.

For example, the following batch will not throw an error:

create table #t(id int,col2 int not null default 0)
insert into #t (id) values (1)
insert into #t (id,col2) values (2,default)
drop table #t

--
Gert-Jan



Frank Osterberg wrote:

Previous Posts In This Thread:

On Thursday, February 21, 2008 6:38 AM
Frank Osterberg wrote:

Force Default value on NOT NULL column when NULL is passed?
Hi,

i have a table which has several "NOT NULL" columns and a default value for
each.

In MySQL the default value is used for 'not null' columns when someone
executes an insert statement without specifying that column or when the
passed value is NULL or DEFAULT.

When i try to do the same in SQL Server 2005 i always just get the error
'field cannot be null' error!
Is ther something wrong or does it just not work that way in SQL Server?
How can i make this work like that?

I though maybe a trigger, but i want it to be as fast as possible and i am
not even sure how i would write that trigger.. is it possible to change
insert values passed to a trigger BEFORE they get inserted?

Any suggestion would be greatly appriciated!

regards, frank

On Thursday, February 21, 2008 6:53 AM
Tibor Karaszi wrote:

If you specify a NULL for the column then the engine will complain that a NULL
If you specify a NULL for the column then the engine will complain that a NULL is not allowed. That
NULL will not be "automagically" converted to your default value. MySQL apparently do not adhere to
the ANSI SQL behavior. You could have an INSTEAD OF trigger which "intercepts" the INSERT and
re-generates an insert the way you like it. I prefer to have the application behave correctly,
though.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Frank Osterberg" <raven7370(a)yahoo.com> wrote in message
news:OztwC5HdIHA.1208(a)TK2MSFTNGP05.phx.gbl...

On Thursday, February 21, 2008 2:34 PM
Gert-Jan Strik wrote:

Frank,First of all, SQL Server would throw an error if you explicitely inserta
Frank,

First of all, SQL Server would throw an error if you explicitely insert
a NULL value into a column that is not nullable, although it would be a
different error than the one you mention.

However, omitting the column when inserting, or inserting DEFAULT will
*not* lead to an error, but will simply insert the default.

For example, the following batch will not throw an error:

create table #t(id int,col2 int not null default 0)
insert into #t (id) values (1)
insert into #t (id,col2) values (2,default)
drop table #t

--
Gert-Jan



Frank Osterberg wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
DataContractSerializer Basics
http://www.eggheadcafe.com/tutorials/aspnet/ad947ce6-cd3e-4647-b69c-94d2f3b1b265/datacontractserializer-ba.aspx
 | 
Pages: 1
Prev: Need help with a stored procedure
Next: Not nullable field doesn't get set to default on virtual server