Conversion from 'uniqueidentifier' to 'int' is not supported on the connected database server

Question

I'm about to migrate my database from using old membership to the one included in mvc4, which uses int instead of guid.

When changing the type I get following error:

Conversion from int to uniqueidentifier is not supported on the connected database server.

How can I change UserId to int via SQL Server Management Studio?

Those datatypes are not compatible, you'll need to convert them using a calculation that works for you — Lamak, Feb 25 '13 at 14:27

score 77 · Answer 1 · answered Aug 30 '13 at 05:32

77

First Change Data Type to any other supported Type like varbinary or anything. then you can convert from that to what you want.

answered Aug 30 '13 at 05:32

Muhamed Shafeeq

1,194
1
9
15

1

Sometimes **save** operation after change to another type is needed; in the other hand will not work. – p__d Jun 26 '18 at 15:00
It is working also for from 'int' to 'uniqueidentifier' – Leo Dec 03 '19 at 11:40
Worth pointing out that indexes may be deleted from the column whose type is being changed. – Danny Beckett Nov 10 '21 at 11:58

score 10 · Accepted Answer · answered Feb 25 '13 at 14:29

You have to add a new column ( ALTER TABLE ADD [NewId] INTEGER ) then run the following to populate the new id column :

WITH Cte
AS
(
    SELECT *
    , ROW_NUMBER() OVER(ORDER BY [Your GUID Column Here] DESC) AS RowNumber
    FROM YourTable
)
UPDATE Cte
SET [NewId]= RowNumber
GO

There you have a new ID column that you can use a clustered primary key

score 2 · Answer 3 · edited May 06 '20 at 12:56

2

With reference to this post:

Deleting the current column and adding a new column with the required data type should resolve your issue.

edited May 06 '20 at 12:56

Ardent Coder

3,777
9
27
53

answered May 06 '20 at 12:06

Stefan27

845
8
19

score 1 · Answer 4 · answered Oct 30 '20 at 21:04

as we know; the unique identifier variable is 32 bytes; int is 8 bytes and bigint is 16 bytes.

we make convert ,first byte and after uniqueidentifier.

Simple code;

Select CAST( CAST(cast(0 as bigint) AS varbinary)+CAST(cast(88 as bigint) AS varbinary) as uniqueidentifier)

Conversion from 'uniqueidentifier' to 'int' is not supported on the connected database server

4 Answers4