Geeks With Blogs

Tim Huffam Dotting the I and crossing the T of I.T.
When modifying a table SQL Server (2005) and unchecking a column's 'Allow Nulls' (ie trying to make the column Not Null) the following error may occur:
'<tablename>' table
- Unable to modify table. 
Cannot insert the value NULL into column '<columnname>', table '<dbname>.dbo.Tmp_<tablename>'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This occurs if there is already data in this table and this column contains nulls.
 
The solution is to simply update the data in this table so that this column does not contain nulls then change the column to not null.  eg if this column was of type int you could do this:

UPDATE [<dbname>].[dbo].[<tablename>]
    SET [<integercolumnname] = -1

Posted on Thursday, May 29, 2008 9:05 PM C# .NET , SQL Server , ASP.NET | Back to top


Comments on this post: Cannot change column to Not Null: Cannot insert the value NULL into column '', table '.dbo.Tmp_'; column does not allow nulls.

# protect from insert null value
Requesting Gravatar...
Dear All,
While Click data insert in to table and Textbox became null;
When i Click once more The Null all inserting in to Table. i want to protect this...
Left by Shahul on Aug 13, 2009 12:38 PM

# Solution
Requesting Gravatar...
Go to excel and copy all (ctrl + e )

paste in another book ,

and try again (import to sql , etc )

that works for me , i think that when we fill the shelds just moving down our mouse there's a formula or kind of format that sql detect as an empty (null)...and when we past the data in another book it pasted like a numbers and then sql recognised it XD .. something like that ..
Left by vivi on Dec 16, 2011 6:39 PM

Your comment:
 (will show your gravatar)


Copyright © Tim Huffam | Powered by: GeeksWithBlogs.net