sql server and website
hi
I recently moved my website to new hosting. At that point I turned off all membership login etc because the site security needed improving and have not added or updated the customer tables from that point.
I am using a local copy of 2012 express to access the hosted database
I now want to turn the customer processing on.
There is now a problem with the customer table. It has all the existing values from before but the CustomerID (the primary key) does not have the identity key and values set so I am unable to add new customers.
I can change the settings to automatically create a new customer table value but I cannot save them back to the table because I need to drop the table before saving the values I have set.
What do I have to do to drop the table.
thanks
Michael
I recently moved my website to new hosting. At that point I turned off all membership login etc because the site security needed improving and have not added or updated the customer tables from that point.
I am using a local copy of 2012 express to access the hosted database
I now want to turn the customer processing on.
There is now a problem with the customer table. It has all the existing values from before but the CustomerID (the primary key) does not have the identity key and values set so I am unable to add new customers.
I can change the settings to automatically create a new customer table value but I cannot save them back to the table because I need to drop the table before saving the values I have set.
What do I have to do to drop the table.
thanks
Michael
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.
I do not think you can make existing column (CustomerID) as identity key.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi carrzkiss
What I am getting with my attempts and following your guide is the message:
Saving changes is not permitted.
The changes require table to be dropped and recreated.
(their version is a bit longer)
I don't know how to drop the table and (sorry Ashok) I don't fully understand your process to drop the table.
What I am getting with my attempts and following your guide is the message:
Saving changes is not permitted.
The changes require table to be dropped and recreated.
(their version is a bit longer)
I don't know how to drop the table and (sorry Ashok) I don't fully understand your process to drop the table.
That is weird.
I did not have that issue when I changed hosting providers, and moved my database.
When you moved your database, did your back it up and then restore it to the new location?
Or what did you do
I did not have that issue when I changed hosting providers, and moved my database.
When you moved your database, did your back it up and then restore it to the new location?
Or what did you do
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your help. It was useful but I had to get the hosting site to drop the table. I was not confident enough to do it myself.
I just do not understand why the table had to be dropped in order to perform what I provided.
I have done it a LOT and have never had that issue.
It sounds to me that the hosting provider does not have the server properly setup.
Good Luck.
Carrzkiss
I have done it a LOT and have never had that issue.
It sounds to me that the hosting provider does not have the server properly setup.
Good Luck.
Carrzkiss
In order to change a column so it becomes an IDENTITY column, the table has to be rebuilt. In other words you have to copy all the data to a temporary with the correct structure and then drop the old table and rename the temporary table to the old table name. Of course if you do this and you have other constraints, those may have to be first dropped and then recreated at the end.
Incidentally this is exactly what SSMS does.
Incidentally this is exactly what SSMS does.
OK, you are right AC.
I misunderstood the IDENTITY part of it.
I misunderstood the IDENTITY part of it.
ASKER
Do you know WHY I would need to drop the table to make this change? When we moved the database I created a .bak file and sent this to the hosting company. Does the problem with this table suggest that they did not install the database properly?
Is it a simple thing for them to do? I have a fair chance of making a mistake with the steps you suggest.
michael