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

Question

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

Any triggers on the table? What are the column datatypes? Those GUIDs are fine. — Martin Smith, Jan 05 '14 at 23:05
No repro. [SQL Fiddle](http://sqlfiddle.com/#!6/a2361/1) please double check the table structure. You haven't accidentally made one of the time columns `uniqueidentifier`? — Martin Smith, Jan 05 '14 at 23:09
The problem was that the ID column wasn't getting any value. I saw on @Martin Smith JS Fiddle that he declared the ID column with DEFAULT newid and I didn't. — Simon Edström, Jan 05 '14 at 23:15
Still don't understand how the INSERT statement in the question could lead to the problem - you're overriding the default so it shouldn't be a problem. Perhaps this is a lesson to include the actual insert statement that fails? — Aaron Bertrand, Jan 06 '14 at 01:29
@AaronBertrand - They aren't inserting `id` explicitly. So if the default itself was something invalid such as `''` they would get that error. — Martin Smith, Jan 06 '14 at 10:07
@MartinSmith ah, I thought the "id" was "myid" - especially since the title also only talks about "Two GUIDs" - easily confused by bad column names. — Aaron Bertrand, Jan 06 '14 at 14:15

score 43 · Answer 1 · answered Sep 12 '17 at 12:50

43

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

answered Sep 12 '17 at 12:50

Joshua Duxbury

4,892
4
32
51

4

Correct, one root cause for errors is an invalid GUID. Check if it has the correct pattern and string length first. – MichaelHuelsen Apr 12 '18 at 12:10

Simon Edström · Accepted Answer · 2014-03-04T11:28:20.873

34

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..

edited Mar 04 '14 at 11:28

answered Jan 05 '14 at 23:14

Simon Edström

6,461
7
32
52

score 6 · Answer 3 · answered Jan 05 '14 at 23:17

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 ║
╚══════════════════════════════════════╩══════════════════════════════════════╩══════════════════════════════════════╩═════════════════════════╩═════════════════════════╝

kal · Answer 4 · 2022-03-18T14:45:01.563

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}");

score 3 · Answer 5 · answered Oct 19 '21 at 02:35

3

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

http://guid.us/Test/GUID

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

answered Oct 19 '21 at 02:35

Thuy

1,493
1
11
11

score 2 · Answer 6 · answered Jan 17 '22 at 08:26

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.

score 0 · Answer 7 · edited Jul 20 '17 at 20:30

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

Please try to use the formatting options for code when posting answers ;-) — Fabien, Jul 20 '17 at 19:17

score 0 · Answer 8 · answered Nov 04 '21 at 01:23

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.

I was using the cast in concert with isnull and when values were null, I got the same error. Now using [Concat](https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver15) instead - it implicitly converts everything to strings and, replaces null with the empty string - — B5A7, Feb 03 '22 at 23:34

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

8 Answers8

Linked