Data Type Conversion Problem

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

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

|||

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?

|||

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

|||I didn't think about making a script and modifying the script. Thank you for that suggestion. I will give that a try.