CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqluniqueidentifier
Mehdi Bugnard
Mehdi Bugnard

Reputation: 3979

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

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

Upvotes: 34

Views: 174707

Answers (3)

Manoj Tyagi
Manoj Tyagi

Reputation: 243

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')

Upvotes: 0

bgs
bgs

Reputation: 3223

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

Upvotes: 4

Darren
Darren

Reputation: 70786

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.

Upvotes: 46

Related Questions