Solved

conversion from int to uniqueidentifier is not supported

Posted on 2009-07-05
9
3,223 Views
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

0
Comment
Question by:jtuttle99
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 75

Expert Comment

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

Expert Comment

by:Patrick Matthews
Comment Utility
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.

Regards,

Patrick
0
 

Author Comment

by:jtuttle99
Comment Utility

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!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
jtuttle99,

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

Regards,

Patrick
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:jtuttle99
Comment Utility
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 #?
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
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 ?
0
 

Author Comment

by:jtuttle99
Comment Utility

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.
0
 

Author Comment

by:jtuttle99
Comment Utility
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore 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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now