This post has been republished via RSS; it originally appeared at: SQL Server Support articles.
First published on MSDN on Dec 07, 2017
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