Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can not change table's structure 1

Status
Not open for further replies.
markros

markros

Programmer
Joined
May 21, 2007
Messages
3,150
Location
US
Hi everybody,

I'm trying to change the table to have the ID field as GUID (it was int Identity field). However, then I try to save it I'm getting an error preventing me saving changes. I also see a table with the same name and _tombstone prefix (two fields - ID and DeletionDate).

What should I do to be able to change the table structure?

I already truncated this table.

Thanks in advance.
 
Sort by date Sort by votes
1. Script out your table definition to a new query window
2. Change the column's definition to GUID (be sure to add a default if needed)
3. Rename the old table
4. Run the SQL script to create the new table.
5. Copy the data from the old table into the new table.
6. Once you are satisfied with the results, drop the old table.
 
Upvote 0 Downvote
Thanks a lot, this worked. Wondering why SSMS is not smart enough to do this by itself.
 
Upvote 0 Downvote
If you had an Identity, you using one of the number data types. So if you simply change the type to GUID in SSMS, it's not going to be able to convert an Int to a GUID. Perhaps if you had gone into the table designer, deleted the Identity column, and added it again as a GUID it would have worked.
 
Upvote 0 Downvote
No, it didn't work this way either.

But now I already re-created my table, just didn't specify the PK. Now I want to specify the ID field as a PK in the designer and again it doesn't let me :(((

The table has triggers, so I would have to do everything through the scripts again?
 
Upvote 0 Downvote
What version of SQL Server are you using? SQL Server 2008 SSMS doesn't allow you to do as much (at least by default) via the designer as far as table changes are concerned. But that could be a good thing. You get to become more proficient at writing DDL.

Code: 
ALTER TABLE YourTable ADD CONSTRAINT pk_YourTable  PRIMARY KEY(YourUniqueIdentifierColumn)
 
Upvote 0 Downvote
Ok, interestingly, when I tried to right click on the field in the design table window and set it as primary this way, it didn't let me, but when I tried to do the same using indexes button at the top toolbar, it let me. I would expect these two dialogs behave exactly the same.

Looks like some internal SSMS bug to me in SQL Server 2008 Express.
 
Upvote 0 Downvote
Well, looking on the bright side of the things I did become slightly more proficient in writting DDL. Now, on the other hand, I lost almost 40 minutes (or more) on this exercise. Not sure what outweight :)

Anyway, thanks a lot again.
 
Upvote 0 Downvote
I found the option in SQL Server 2008 to change this behavior (menu Tools\Options\Designers), but I don't think I would change this option (the last one).
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question Question
convert hierarchy structure to parent children pair please...
2
Replies
22
Views
3K
mikrom
mikrom
WomanPro
  • Locked
  • Question Question
Insert into query adds records to unreadable format, how do I change that?
Replies
5
Views
819
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question Question
How to change STRING_AGG to stuff xml to split feature value by pip?
Replies
0
Views
398
ahmedsa2018
ahmedsa2018
leifoet
  • Locked
  • Question Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
534
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question Question
Will not show row with Line Type 2
Replies
17
Views
1K
Cpreston
Cpreston

Part and Inventory Search

Sponsor

Back
Top