Conversion failed when converting from a character string to uniqueidentifier - Two GUIDs

I don't understand why I can't insert this. I can't spot the problem. The error message is Conversion failed when converting from a character string to uniqueidentifier.

The GUIDs are the ones that I got when I did a select from some other tables.

insert into [db].[dbo].[table] (myid,friendid,time1,time2) values ( CONVERT(uniqueidentifier,'0C6A36BA-10E4-438F-BA86-0D5B68A2BB15'), CONVERT(uniqueidentifier,'DF215E10-8BD4-4401-B2DC-99BB03135F2E'), '2014-01-05 02:04:41.953','2014-01-05 12:04:41.953') 

I use SQL Server 2012

The columns are

id uniqueidentifier, myid uniqueidentifier, friendid uniqueidentifier, time1 datetime nullable, time2 datetime nullable 

8 Answers

The problem was that the ID column wasn't getting any value. I saw on @Martin Smith SQL Fiddle that he declared the ID column with DEFAULT newid and I didn't..

MSDN Documentation Here

To add a bit of context to M.Ali's Answer you can convert a string to a uniqueidentifier using the following code

 SELECT CONVERT(uniqueidentifier,'DF215E10-8BD4-4401-B2DC-99BB03135F2E') 

If that doesn't work check to make sure you have entered a valid GUID

DECLARE @t TABLE (ID UNIQUEIDENTIFIER DEFAULT NEWID(),myid UNIQUEIDENTIFIER , friendid UNIQUEIDENTIFIER, time1 Datetime, time2 Datetime) insert into @t (myid,friendid,time1,time2) values ( CONVERT(uniqueidentifier,'0C6A36BA-10E4-438F-BA86-0D5B68A2BB15'), CONVERT(uniqueidentifier,'DF215E10-8BD4-4401-B2DC-99BB03135F2E'), '2014-01-05 02:04:41.953','2014-01-05 12:04:41.953') SELECT * FROM @t 

Result Set With out any errors

╔══════════════════════════════════════╦══════════════════════════════════════╦══════════════════════════════════════╦═════════════════════════╦═════════════════════════╗ ║ ID ║ myid ║ friendid ║ time1 ║ time2 ║ ╠══════════════════════════════════════╬══════════════════════════════════════╬══════════════════════════════════════╬═════════════════════════╬═════════════════════════╣ ║ CF628202-33F3-49CF-8828-CB2D93C69675 ║ 0C6A36BA-10E4-438F-BA86-0D5B68A2BB15 ║ DF215E10-8BD4-4401-B2DC-99BB03135F2E ║ 2014-01-05 02:04:41.953 ║ 2014-01-05 12:04:41.953 ║ ╚══════════════════════════════════════╩══════════════════════════════════════╩══════════════════════════════════════╩═════════════════════════╩═════════════════════════╝ 

My problem was the "guid" which I thought was a "guid" was not a guid:

Thus, I rechecked the string that I entered and saw that I did not have a valid guid.

You have to check unique identifier column and you have to give a diff value to that particular field if you give the same value it will not work. It enforces uniqueness of the key.

Here is the code:

Insert into production.product (Name,ProductNumber,MakeFlag,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint,StandardCost,ListPrice,Size ,SizeUnitMeasureCode,WeightUnitMeasureCode,Weight,DaysToManufacture, ProductLine, Class, Style , ProductSubcategoryID ,ProductModelID ,SellStartDate ,SellEndDate ,DiscontinuedDate ,rowguid ,ModifiedDate ) values ('LL lemon' ,'BC-1234',0,0,'blue',400,960,0.00,100.00,Null,Null,Null,null,1,null,null,null,null,null,'1998-06-01 00:00:00.000',null,null,'C4244F0C-ABCE-451B-A895-83C0E6D1F468','2004-03-11 10:01:36.827') 

I had an interesting variation on this. I initially thought that I had an issue inserting into a uniqueidentifier field, based on this question and, the answers. However, in my case, I was getting this error when inserting into a varchar field. The value that I was inserting, contained some text that was a uniqueidentifier but, also contained other text. It looked like SQL Server attempted to parse the text as a uniqueidentifier because it recognized that some of the text was a uniqueidentifier. When I had first setup the insert statement, the issue was masked because I was wrapping the value with UPPER. I subsequently altered the "fix", casting the value as varchar.

Also make sure that when you get data , it should be in correct format. Since the data types are of uniqueidentifier so it's correct format will be xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid Uniqueidentifier value.

I faced this issue today while executing the SQL Command using ExecuteSqlInterpolatedAsync of EF Core 5.

Bad code: the contactId is placed inside the ' '

await _dbContext.Database.ExecuteSqlInterpolatedAsync($"UPDATE Contact SET Status = {(int)Status} WHERE Id = '{contactId}'");

Good code: remove the ' ' from the contactId and it works

await _dbContext.Database.ExecuteSqlInterpolatedAsync($"UPDATE Contact SET Status = {(int)Status} WHERE Id = {contactId}");