How to generate and manually insert a uniqueidentifier in SQL Server?

Question

I'm trying to manually create a new user in my table but am finding it impossible to generate a "UniqueIdentifier" type without the code throwing an exception...

Here is my example:

DECLARE @id uniqueidentifier
SET @id = NEWID()

INSERT INTO [dbo].[aspnet_Users]
           ([ApplicationId]
           ,[UserId]
           ,[UserName]
           ,[LoweredUserName]
           ,[LastName]
           ,[FirstName]
           ,[IsAnonymous]
           ,[LastActivityDate]
           ,[Culture])
     VALUES
           ('ARMS'
           ,@id
           ,'Admin'
           ,'admin'
           ,'lastname'
           ,'firstname'
           ,0
           ,'2013-01-01 00:00:00'
           ,'en')
GO

Throws this exception -> Msg 8169, Level 16, State 2, Line 4 Failed to convert a character string to uniqueidentifier.

I am using the NEWID() method but it's not working...

http://www.dailycoding.com/Posts/generate_new_guid_uniqueidentifier_in_sql_server.aspx

What type is your `UserID` column in table? Are you sure some other column is not `UniqueIdentifier` type where you are inserting strings? ApplicationID maybe? — Nenad Zivkovic, Jun 24 '13 at 11:27
The error is probably for another column, my guess is ApplicationID, what is the data type of ApplicationID? — Vishal Gajjar, Jun 24 '13 at 11:28
It has the "uniqueidentifier" type, but I've read that should be used NewGuid () to generate a type "uniqueidentifier". Is that wrong? — Mehdi Bugnard, Jun 24 '13 at 11:29
I think you have another uniqueidentifier column that you are inserting a string value, such as UserID — VahiD, Jun 24 '13 at 11:31
in that case, you cannot insert 'ARMS' as ApplicationID. You need to use NEWID() for it as well. SQL Server does not have NewGuid(). — Vishal Gajjar, Jun 24 '13 at 11:32
You above code is absolutely fine. Error is actually on another field. So, again check the Data Type of Each fields. Im guessing that, you are using UniqueIdentifier on other than the ID column. — , Jun 24 '13 at 11:33

score 46 · Accepted Answer · answered Jun 24 '13 at 11:31

ApplicationId must be of type UniqueIdentifier. Your code works fine if you do:

DECLARE @TTEST TABLE
(
  TEST UNIQUEIDENTIFIER
)

DECLARE @UNIQUEX UNIQUEIDENTIFIER
SET @UNIQUEX = NEWID();

INSERT INTO @TTEST
(TEST)
VALUES
(@UNIQUEX);

SELECT * FROM @TTEST

Therefore I would say it is safe to assume that ApplicationId is not the correct data type.

bgs · Answer 2 · 2013-06-24T12:13:58.557

Kindly check Column ApplicationId datatype in Table aspnet_Users , ApplicationId column datatype should be uniqueidentifier .

*Your parameter order is passed wrongly , Parameter @id should be passed as first argument, but in your script it is placed in second argument..*

So error is raised..

Please refere sample script:

DECLARE @id uniqueidentifier
SET @id = NEWID()
Create Table #temp1(AppId uniqueidentifier)

insert into #temp1 values(@id)

Select * from #temp1

Drop Table #temp1

score 0 · Answer 3 · answered Sep 14 '21 at 17:27

Check your column data type should be unique identifier And you are using the correct order when inserting values

INSERT INTO [dbo].[aspnet_Users]
([ApplicationId],[UserId],[UserName],[LoweredUserName],[LastName]
,[FirstName],[IsAnonymous],[LastActivityDate],[Culture])
VALUES ('ARMS',NEWID(),'Admin','admin','lastname','firstname,0
,'2013-01-01 00:00:00','en')

How to generate and manually insert a uniqueidentifier in SQL Server?

3 Answers3

Linked