Link to home
Start Free TrialLog in
Avatar of michaeldean99
michaeldean99Flag for United Kingdom of Great Britain and Northern Ireland

asked on

sql server problem

hi
I have an online  sql server 2008 database with several tables. But one is not allowing me to make changes to it.
The website is returning
'Cannot insert the value NULL into column 'CustomerID', table 'xxxxxxxx.Customers'; column does not allow nulls. INSERT fails.'

But the problem is with one table on the database. It was working OK but somehow the table settings changed.
The Identity specification is now set to No. I can change the settings to Yes and set is identity = Yes and I cannot save the changes.
I am supposed to be able to do it if I drop and recreate the tables!!

What do I need to do to sort this out please?

Michael
Avatar of Wizilling
Wizilling
Flag of New Zealand image

first find the row with the null value entered
ie. select * from table xxx.customers where CustomerID is null.

check why is the cusomerid column null is this case.
is it a legitimate row , if yes then assign a new customerID to id
if no then delete the row.

make your changes again. ie. set identity to yes and save your table.

SOLUTION
Avatar of Wizilling
Wizilling
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of michaeldean99

ASKER

** make your changes again. ie. set identity to yes and save your table **

no, the problem is that I am not being allowed to change the identity to yes. I can make the actual  change but I am not being allowed to save it!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

If I cannot fix the existing  table, how about a simpler process which I understand? I am not confident enough to do it in one transaction!

- rename existing customers table
- create new customers table
- Run update query to transfer data from old table to new customers table
- delete old

michael      
I didn't get very far!!!
I created a new, empty customer table and put it online.
I now have mdean_stampsforsale.customers  (with the data)
and mdean_stampsforsale.customersx (currently empty)
Both tables return the same message when any changes are saved.
"Saving changes is not permitted. The changes you have made require the following tables to be dropped and recreated. You have either made changes to a table that can't be recreated or enabled the option 'Prevent saving changes that require the table to be recreated."
The change I am trying to make to the tables is to set the Identity specification on

I don't need the table copy (customersx) if I can save the customers table. I cannot find any way to create a working table.
Any help would be appreciated.
thanks
michael

Just a thought. Is this problem possibly down to available disk storage for the database?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
brilliant! That's what I was looking for. I assume that it is a separate problem but I am now getting an error

'Customers' table
- Unable to create index 'PK_Customers'.  
Could not allocate space for object 'Customers' in database 'mdean_stampsforsale' because the 'PRIMARY' filegroup is full.
Could not create constraint. See previous errors.
The statement has been terminated.

Is this just down to available space on the hosting company servers?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i haven't chosen my comments. Cannot see why the system has selected my signing off comments as the solution!