Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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!
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach 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 video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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