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 :)

Column allows nulls I want to change no nulls allowed

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 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 suxWhat does a SELECT statement not returning any records have to do with allowing NULLs? And why are you doing an INSERT statment while modifying the table?

Your post does not make much sense.|||It would help if you poseted the 'select' statement.

Are you using columnname = NULL or columnname IS NULL in the 'where' clause?

I don't think you can alter a table to prohibit NULLs if NULLs already exist in the table. You'll have to change those values or provide a default when you do the alter.|||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

HERE IS THE SQL On the 2nd table problem

SELECT idMaster, idVendor, idExpType, idCompany, DateEntered, EnteredBy, CheckNumber, CheckAmount, VendorName, ExpenseType, CheckDate, voided,
ckSuffix, MasterTs, tempCol
FROM aDataEntry.master
WHERE (idCompany IS NULL)|||Post the script (DDL) for the table.