conversion from int to uniqueidentifier is not supported

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

jtuttle99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
why do u want to change the PK field from integer to uniqueidentifier,  (unless you have merge replication / or a cube )
0
Patrick MatthewsCommented:
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
jtuttle99Author Commented:

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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Patrick MatthewsCommented:
jtuttle99,

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

Regards,

Patrick
0
jtuttle99Author Commented:
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
Mark WillsTopic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jtuttle99Author Commented:

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
Mark WillsTopic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.