none
Data Type Conversion Problem RRS feed

  • Question

  • I am not sure if this is the right place to post, although I am new to SQL Server.

    I upsized a database from MS Access to SQL Server 2005 on my local machine. I am trying to change the primary key from int to uniqueidentifier, but I get this error: "conversion from int to uniqueidentifier is not supported on the connected database server". I have tried googling around to see if anyone has posted an answer, but haven't found a solution. I have also tried detaching the database, but that doesn't help either.

    Can someone help me understand what the error is meaning by "connected database server", and how I can make it possible to change the data type?

    Thank you.

    Ben

    Saturday, October 28, 2006 7:36 PM

Answers

  • I think you have to manually do the conversion: delete the current PK column (remove the PK first) and create a new column with data type uniqueidentifier.

    If your schema is large with foreign key constraints this might be a lot of work. You might try to script the database to a .sql file and change the data type with a text editor. Then create the database from that modified sql file.

    --
    SvenC

    Sunday, October 29, 2006 8:51 AM

All replies

  • Hi Ben,

    uniqueidentifier is a GUID, that is a 128 bit value. To guarantee uniqueness they are created by a system call. So there is no way how an int could be transformed to be a uniqueidentifier.

    Why do you want to change that column?

    --
    SvenC

    Saturday, October 28, 2006 8:00 PM
  • Sorry, I should have been more clear. There is no data in the database. I upsized just the schema, triggers, and such, but not the data. I was hoping to convert the primary key from int to the uniqueidentifier.

    Is this possible?

    Saturday, October 28, 2006 9:55 PM
  • I think you have to manually do the conversion: delete the current PK column (remove the PK first) and create a new column with data type uniqueidentifier.

    If your schema is large with foreign key constraints this might be a lot of work. You might try to script the database to a .sql file and change the data type with a text editor. Then create the database from that modified sql file.

    --
    SvenC

    Sunday, October 29, 2006 8:51 AM
  • I didn't think about making a script and modifying the script. Thank you for that suggestion. I will give that a try.
    Monday, October 30, 2006 3:36 PM