Conversion failed when Converting from a character string to uniqueidenitier

1

just wondering if there is an easy way to id what is triggering this error when accessing a certain record? SQL 2019/2022

2

Kindly post the table structure with sample data and the query that you are using to read the data.

3

Don't have a query. just asking if there is a tool or easy way to find the culprit if this error pops up in a client's DB like when accessing a certain screen. ususally we can grab a sql log or trace to get the insert statements etc.

4

you can always use try_cast. Select * from table where try_cast(field as uniqueidentifier) is null. For large tables, it'll take a while but at least you can identify them

1 Like
5 
/* CREATE TEST-case */

DROP TABLE IF EXISTS #tTemp


SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
INTO #tTemp
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION
SELECT 'A'
UNION
SELECT CONVERT(VARCHAR(256),NEWID()) AS ID


SELECT 
ID,
TRY_CAST(ID AS uniqueidentifier) AS TryCast
FROM #tTemp
/* 
Use:

WHERE TRY_CAST(ID AS uniqueidentifier) IS NULL 

to get the wrong data
*/
6

why is SELECT CONVERT(VARCHAR(256),NEWID()) AS ID
UNION repeated several times?

7

I've repeated it several times to get more test data. So you get a table with multiple results. I could have done only one but I like to have more data. Just a personal favor :wink: