conversion from int to uniqueidentifier is not supported

Posted on 2009-07-05
Medium Priority
Last Modified: 2012-05-07
I got this error when I try to convert from Int to Uniqueindenfifier
I googled ant It seems that everyone wants to know why I want to convert.
I had Access Database and converted to SQL server data.
During the conversion,  Autonumber Primary Key (unique, inedxed) in Access was converted Int in SQL.

Do I have to make new Column and set Primary key?
If so, is it possible to import the data from existing column?

I'm using SQL server 2008 express edition.
Thank you

Question by:jtuttle99
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24781435
why do u want to change the PK field from integer to uniqueidentifier,  (unless you have merge replication / or a cube )
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24781461
Hello jtuttle99,

int and uniqueidentifier are completely different data types, and cannot be converted from one to another.

As aneesh points out, the uniqueidentifier data type is mostly used for replication.

If your long integer AutoNumber in Access worked for you, then using int is entirely appropriate in SQL Server.



Author Comment

ID: 24781521

I'm new to SQL and I thought uniqueidentifier in SQL is for Autonumber(unique, indexed) in Access.
So, if I want to make the column (in this case BlogID) as Autonumber in Access, what kind of property do I need to add in Int? I want to make it automatically generate.

Thank you for your help!
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 93

Expert Comment

by:Patrick Matthews
ID: 24781529

Set up that column as identity.  That will force the column to auto-increment as in Access.



Author Comment

ID: 24781636
Before I save the column, could you tell me what will happen to the existing data?
Do I have to set Identity seed to the last existing # or does it automatically detect the last #?
LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 24784863
This could be fun....

in SQL Server, the equivelant to an autonumber field is an identity. It is a number based field of int, or, bigint, or, decimal etc. datatypes depending on (numeric) size.

For identities, typically an INT is used and gives you a numeric value up to 2^311 (2,147,483,647) in 4 bytes, but need not be restricted to INT.

a uniqueidentifier is a big long beast of 16 digits.

So, to replicate the autonumber type function, it is an identity that you want. However there are a few catches...

1) you do not normally populate an identity column. You allow SQL to manage it for you.
2) if populating with some existing data as an initial load, you normally do not assign your int column to be an identity until afterwards. This is easily done in Table Design in SSMS (SQL Server Management Studio), which is available for Express.

Depending on which version of express you downloaded, SSMS is either part of your install, or, there is a seperate download and install after the event (say for the runtime version).

So, the steps would be, populate your new tables in SQL, then using SSMS go into table design (right click on the table, go into design) and highlight your INT column. Down the bottom you get to play with the properties of that column. Identity being one of them (half way through). You can expand the identity group to specify increment etc. You can also do that using T-SQL by opening a new query window and typing ALTER TABLE < TableName > ALTER COLUMN < ColumnName > < Datatype > IDENTITY(< NewSeed >, < NewStep >)

Second method would be to create the empty table with the identity already specified and start insert rows according to the existing autonumber field.
Either way, it will take care of the next number for you. In fact there can sometimes be gaps in the numbers because they are assigned / reseerved before the row is written (and even if not written that number is used).

The difference is if anything is dependant on the original automnumber value. if so, use method one above (ie populate then specify identity), if not, use the second method of inserts (ie specify identity and then insert). There is a third way where you can temporarily override the inherent nature of an identity, but seeing as this is a conversion/ migrate you probably do not have to worry (you can SET IDENTITY_INSERT table { ON | OFF } ).

Then once set, you can pretty much ignore it for inserts and updates. SQL will manage it for you. In fact, mentioning it in an insert statement will cause an error, similarly modifying its content will error (in normal use).

Now, a uniqueidentifier is a 16byte beats and can be expressed as with character or binary. e.g. '6F9619FF-8B86-D011-B42D-00C04FC964FF' would be a character representation of a unique identifier. These are random unless you specify sequential ID's. The main difference (apart from size) is uniqueidentifiers are globally unique (hence GUID), whereas identity is unique within a table.

Is there anything more you would like to know ?

Author Comment

ID: 24788064

I could create new table and set identity, but I would like to use the table (or data) I converted from Access.
Once I create a new table and set the int as identity, can I import other column's data( converted from Access) to the new table?
I tried...
1,  I tried changing blogID's(converted from Access) "IS Identity" property to Yes, I get an error as follow.
"Saving changes is not permitted. The changes you have made required the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

2, I added new column to the table(converted from Access) and set the int as "Is Identity"
I get same error as above.

3, So, I created new table as Mark showed me but I can't import the rest of the data that I converted from Access.

Thank you so much for your help.
LVL 51

Expert Comment

by:Mark Wills
ID: 24789355
Glad to hear it - was asleep "down under" while you were going through the above, apologies for the silence, and very happy to have been of help...

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question