michaeldean99
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
I didn't get very far!!!
I created a new, empty customer table and put it online.
I now have mdean_stampsforsale.custom ers (with the data)
and mdean_stampsforsale.custom ersx (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
I created a new, empty customer table and put it online.
I now have mdean_stampsforsale.custom
and mdean_stampsforsale.custom
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
ASKER
Just a thought. Is this problem possibly down to available disk storage for the database?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
'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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i haven't chosen my comments. Cannot see why the system has selected my signing off comments as the solution!
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.