×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Can not change table's structure

thread183-1548439

Can not change table's structure

Can not change table's structure

(OP)
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.
 

RE: Can not change table's structure

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.

RE: Can not change table's structure

(OP)
Thanks a lot, this worked. Wondering why SSMS is not smart enough to do this by itself.

RE: Can not change table's structure

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.

RE: Can not change table's structure

(OP)
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?

RE: Can not change table's structure

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)

RE: Can not change table's structure

(OP)
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.

RE: Can not change table's structure

(OP)
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.
 

RE: Can not change table's structure

(OP)
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).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close