conversion from int to uniqueidentifier is not supported

Posted on 2009-07-05
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 92

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!
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 92

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 500 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 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