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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.