We help IT Professionals succeed at work.

sql server problem

michaeldean99
michaeldean99 asked
on
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
Comment
Watch Question

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.

if
select * from table xxx.customers where CustomerID is null doesn't return rows try
select * from table xxx.customers where CustomerID = ''
or select * from table xxx.customers where len(rtrim(ltrim(CustomerID))) =0

Author

Commented:
** 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!
Commented:
is not that simple
you need to create a copy of your table with the identity set

like begin tran
create table yourTempTable (id_column int not null identity(1,1), rest of columns) on primary
go

set identity_insert yourTempTable on
go

if exists(select 1 from yourTable)
    insert into yourTempTable (id_column, rest of columns) select id_column, rest of columns FROM yourTable TABLOCKX
go

set identity_insert yourTempTable off
go

drop table yourTable
go

execute sp_rename N'yourTempTable', N'yourTable', 'OBJECT'
go

commit transaction
 

Author

Commented:

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      

Author

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

Author

Commented:
Just a thought. Is this problem possibly down to available disk storage for the database?
Do this.
start Sql server management studio
goto the Tools menu
click options
scroll down and click the Designers Page.
untick this option: Prevent saving changes that require table re-creation.


now try making your changes and saving it.

Author

Commented:
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?
yes, check the autogrow properties for the database.

right click on your database and select properties. click files

check if the primary data file group is not set to a maximum size limit. if it is, try setting it to unlimited.
(if you want to set a max size limit then you will have to play around with the sizes)

or it just could be that you hard disk is full.d




yes, 147mb initial size, autogrow by 10%, restricted growth to 147mb
The logfile is ok, 673 initial size, 1207mb max
many thanks for your help.

Author

Commented:
i haven't chosen my comments. Cannot see why the system has selected my signing off comments as the solution!