Why can I not save changes in SQL Server 2008 tables?

VoodooFrog
VoodooFrog used Ask the Experts™
on
I am using Server 2008 Management Studio to interact with an SQL 2008 Server.

When I create then save a table, if I go back to make a change to a field it almost always errors out when I try to save. I cannot even Delete the column, and make a new column without saving in between; if I try to do both without saving in between I get an error.
If I try to change the datatype after saving, it will not save the change.
I cannot change the Identity property after the column has been created.

The database was from an SQL 2000 Server; however, the tables am working with are newly created.

Any idea what the source of my problems are and how to fix it so I can save?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
can this solve your problem: http://support.microsoft.com/kb/956176/en-us
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You changed the database compatibility level to SQL Server 2008?

Author

Commented:
I have not changed the compatibility. If I need to move this database back to a 2000 server, will changing this compatibility setting cause any problems?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
The answer is: "It might if you start to update the database with SQL 2008 features".
But if you don't change the compatibility you also can't profit from SQL 2008.

Author

Commented:
If I change compatibility to 2008, but don't use the 2008 features should I be ok? and what are the 2008 features that would cause these problems?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
The answer for first question it's: "Should be but in theory. In pratice I never tried to go back to SQL 2000"
For second question, here is a resume: http://msdn.microsoft.com/en-us/library/bb510411.aspx (news features from SQL 2005 to 2008).
Can you please check this
ChangeTable.png

Author

Commented:
Does table re-creation delete data held within the tables?
SSMS will Create a temp table..move the data to the temp table..and create a new table and then will move the data from temp table to the new table.

It is always better to change the Table structure using TSQL scripts instead of the SSMS Table Designer
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't know what that option can help here. That option only applies if you change a table using SSMS.
Voodoo are using Export tool.

Author

Commented:
If SSMS stands for SQL Server Management Studio, then that is what I am using. I am not sure what "Export tool" is.  I am just using the table designer and the diagram designer to create and modify tables.
Top Expert 2012

Commented:
>>Any idea what the source of my problems are and how to fix it so I can save?<<
Not for certain as you have failed to provide the all important error message.  But if as I suspect it is a timeout error than you are right you cannot accomplish your goal using SSMS in design mode. You need to execute some T-SQL script from the query window instead.
Top Expert 2012

Commented:
Well I see now that was suggested previously at http:#a26429055, did you inadvertently overlook it?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial