Conversion failed when converting from a character string to uniqueidentifier in TSQL

Question

I am having an issue with my T-SQL query here:

UPDATE a
SET a.application = 
            SUBSTRING(b.postdata,
                NULLIF(
                    PATINDEX('%[A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9]-[A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9]-%',b.postdata),0),36)
FROM [Lionel1].[dbo].[CSL_Logging_STAGE] a
INNER JOIN [Lionel1].[dbo].[CSL_Logging_STAGE] b ON b.Id = a.Id
WHERE 
    a.application LIKE '%000%' 
    AND a.postdata != '' 
    AND a.postdata IS NOT NULL
    AND a.http_method = 'post';

I have tried a handful of different castings for the GUID that is stored in the application column and I have also restructured the query to have a subquery but that makes no difference.

The error is

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

Anyone point me in the right direction?

Some of the VALUES I am trying to set to application are

NULL
0dab3646-a4ed-4b63-9441-11e27fa36df8
f6a5c360-635a-48e0-bf71-76ef0b865441

Test your query by changing the UPDATE to just a SELECT and see what value(s) you are trying to set application to. — Tab Alleman, Nov 06 '14 at 19:36
It shouldn't matter what value I am trying to set application to. — Scottzozer, Nov 06 '14 at 19:38
I ran the select and it is either setting a GUID string or a NULL — Scottzozer, Nov 06 '14 at 19:39
Application is defined as follows [application] [uniqueidentifier] NULL, — Scottzozer, Nov 06 '14 at 19:40
Well your error is telling you that your select is generating a string that cannot be used as a GUID. — Tab Alleman, Nov 06 '14 at 19:41
Question is duplicate of http://stackoverflow.com/questions/20940350/conversion-failed-when-converting-from-a-character-string-to-uniqueidentifier — HaveNoDisplayName, Nov 06 '14 at 19:41
you have the same problem title as link, either change the Title, its confusing me at first sight — HaveNoDisplayName, Nov 06 '14 at 19:47

score 0 · Accepted Answer · answered Nov 06 '14 at 20:40

0

It looks like I needed to do a better job matching the appkey. It was grabbing extra data on a few of the compares adding quotes and extra characters which are not valid in a GUID.

answered Nov 06 '14 at 20:40

Scottzozer

116
9

Conversion failed when converting from a character string to uniqueidentifier in TSQL

1 Answers1