converting to uniqueidentifier in SQL

Question

I need the following to be a uniqueidentifier so that it will join properly in a larger query. Is there a better way to be doing this?

Here is the SQL

 -- Report Totals
-- Set date range and this query will look at each specific Crystal Report and how long each one takes to run.  
DECLARE @StartDate DATETIME,
      @EndDate DATETIME;


SELECT @StartDate = '10/01/2013', @EndDate = '1/1/2014';

SELECT COUNT(*) AS RunCount, AVG(DATEDIFF(s, SJ.queuedtime, SJ.completetime)) AS TotalTime,
       AVG(DATEDIFF(s, SJ.queuedtime, SJ.starttime)) AS WaitTime,
       AVG(DATEDIFF(s, SJ.starttime, SJ.completetime)) AS RunTime, 
         RP.label AS Report, RP.reportTitle

FROM SJob SJ

JOIN RReport R ON CAST(SUBSTRING(SJ.props, CHARINDEX('reportID=', SJ.props, 0) + 9, 36) AS UNIQUEIDENTIFIER) = R.reportID
JOIN RPhysicalReport RP ON R.physicalReportID = RP.physicalReportID

WHERE SJ.queuedtime >= @StartDate and SJ.queuedtime < @EndDate and SJ.jobClass = 'CRWPrint'

GROUP BY RP.label, RP.reportTitle

ORDER BY RunTime DESC

when I run this I get

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

it's a text field with over 2000 characters. some of it is client data so I can't really post it. it's literally a continous string of data though. I can pull back just the part that I need and it works but when I try to cast or convert it to a UI it fails on conversion. — BSanders, Feb 18 '14 at 19:13
@BSanders Then show as the result of `RIGHT(SUBSTRING(SJ.props, CHARINDEX('reportID=', SJ.props, 0), 45), 36)`. We need to know if that has the format for a `UNIQUEIDENTIFIER`. Does it has the `-` at the right places?, only numbers and letters from `A` to `E`? — Lamak, Feb 18 '14 at 19:14
@BSanders That one actually does convert to `UNIQUEIDENTIFIER` without any problems — Lamak, Feb 18 '14 at 19:17
@BSanders And how do you know that **that** is the value that gives you an error?. As I said, `CAST('E2715BAC-5E84-42EB-A15A-428F022BBF6E' AS UNIQUEIDENTIFIER)` works just fine — Lamak, Feb 18 '14 at 19:19
Because it fails on that line of the query. I can run this select * from RReport where reportID = 'E2715BAC-5E84-42EB-A15A-428F022BBF6E' and it returns perfectly. — BSanders, Feb 18 '14 at 19:22
@BSanders If that's the "last" value you are getting before it fails, then that means that it isn't the value that fails. And wasn't your table `SJob` instead of `RReport`?, please add the relevant information in the question — Lamak, Feb 18 '14 at 19:23
I don't get any values. It fails instantly with the error I listed. — BSanders, Feb 18 '14 at 19:38

score 3 · Answer 1 · answered Feb 18 '14 at 19:21

There are likely one of two problems:

You aren't getting all 36 characters because there are trailing characters after reportID.
The GUID is not properly formatted, likely because of missing dashes or incorrect position of dashes.

Here's some code that could resolve the first issue assuming the value after reportID= is a valid GUID:

DECLARE @SomeString VARCHAR(MAX) = 'sometextwithar=somethingelse&reportID=5289A1C3-07E3-4CE3-B2C4-78E6B631458E&something';

SELECT CONVERT(UNIQUEIDENTIFIER, SUBSTRING(@SomeString, CHARINDEX('reportID=', @SomeString, 0) + 9, 36)) AS reportID;

I would recommend removing the CAST to UNIQUEIDENTIFIER section and inspect the string you are trying to convert.

Thank you for your response. I tried what you said and I can get that field by itself to convert but it still fails as the whole query shown above. — BSanders, Feb 18 '14 at 19:39

score 1 · Answer 2 · answered Feb 18 '14 at 20:01

1

this was solved by using CONVERT(VARCHAR(36), R.reportID) in my JOIN clause. It now works as it should.

answered Feb 18 '14 at 20:01

BSanders

295
1
6
29

converting to uniqueidentifier in SQL

2 Answers2