Column allows nulls i want to change not to allow

When i do a select on my emplee table for rows with null idCompany i dont ge
t
any records
I then try to modify the table to not allow a null idCompany and i get this
error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table
'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This suxis you select like this?
SELECT * FROM D2.aMgmt.Tmp_Employee
WHERE idCompany IS NOT NULL
or do you have
WHERE idCompany <> NULL
because that will return no rows unless ANSI_NULLS is set to OFF
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Try specifying a default value for idCompany when issuing the ALTER TABLE
statement.
ALTER TABLE employee ALTER COLUMN idCompany datatype NOT NULL DEFAULT ''
"thom" wrote:

> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||I hope you wrote the query as
select * from tbl1
where dCompany is null -- and not "dCompany = null"|||How did you write the query to select all records having null idcompany.
This should list all records with null idcompany
Select * from Employee where idcompany IS NULL
thom wrote:
> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||Of course the IS NOT NULL should be IS NULL and the <> NULL should be
= NULL, sorry for that
so
SELECT * FROM D2.aMgmt.Tmp_Employee
WHERE idCompany IS NULL
or do you have
WHERE idCompany = NULL
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sorry wrong post :P|||I Dont know how but i fixed it, i was not using <> null and not using = nul
l
This issue seemed to be with the Check existing data on creation, i ended up
getting that message on another table and when i changed check existing data
on create to no there was no problem.
BUT either way when i SELECTED * FROM myTable WHERE idColumn IS NULL
i got no records returned
"thom" wrote:

> When i do a select on my emplee table for rows with null idCompany i dont
get
> any records
> I then try to modify the table to not allow a null idCompany and i get thi
s
> error message:
> 'Employee (aMgmt)' table
> - Unable to modify table.
> Cannot insert the value NULL into column 'idCompany', table
> 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> This sux|||Was it really?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:840FB234-5802-4A53-BEE6-D36963CE17F7@.microsoft.com...
> Sorry wrong post :P|||No it wasn't. This was posted in the wrong thread...
and thanks for finding this.. I was searching for this post :)