Dealing with error 8169 “Conversion failed when converting from a character string to uniqueidentifier. “


Customer was receiving the following error:

Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.

Here are all the ways that you can recreate this error:

use tempdb

go

create table t1 (cuid uniqueidentifier default NEWID(), cint int)

create table t2 (cuid_char varchar (20), cint int)

insert into t1 (cint) values (110)

insert into t2 values ('asdfadfadsfadsfdsa', 110)

--Straight convert/cast. You can see that passing an incorrect GUID fails

select convert(uniqueidentifier, '8EA7ADFA-F68D-49DC-A679-81D8D70E1497') --this works just fine

select convert(uniqueidentifier, '8EA7ADFA-F68D-49DC-A679-81D8D70E14')  --this fails with the error

--In the WHERE clause. Comparing a non-GUID string to a uniqueidentifier raises the error

select * from t1

where cuid = 'asdfadsfdsa'

 

--In a Join. Joining a uniqueidentifier column value to a char/varchar value fails

select * from t1 join t2 on

t1.cuid = t2.cuid_char

Based on the scenario that matches your case, troubleshoot accordingly. Essentially, need to fix the query, the data or the data type of your column.

Can you think of any other ways to cause this?

Namaste,

Joseph

 

Comments (0)

Skip to main content