Conversion failed when converting from a character string to uniqueidentifier.

Question

Post reply

Conversion failed when converting from a character string to uniqueidentifier.

mahalaxmi.k62

SSC Enthusiast

Points: 131
More actions
August 1, 2011 at 6:37 am

#390094

Hi,
In my query I have to compare two fields, one uniqueidentifier and the other nvarchar.
Uniqueidentifier Field value is :
the query in Where clause is:
wpmp.grew_workproductmoduleprofilesid in
(select grew_includemodules from filteredgrew_workproductconfiguration
where
grew_workproductconfigurationid = '626cda42-128e-e011-912c-0030486321af') Now the value that the sub Query is this:
{'2632ec76-2209-e011-80d9-0030486321af','6e0cd9e9-2209-e011-80d9-0030486321af','3618be70-2409-e011-80d9-0030486321af','9e82bd76-2409-e011-80d9-0030486321af'}
On converting the uniqueidentifier to nvarchar like
CAST (grew_workproductmoduleprofilesid as nvarchar(255)) / CONVERT(nvarchar(4000),grew_workproductmoduleprofilesid)
the error does not occour but it is failing to compare the two fields and the the query does not return any value even if there is data.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you post the whole query? It should work:

drop table #test

create table #test (id int identity(1,1), guidcol uniqueidentifier)

insert into #test (guidcol) select NEWID()

DECLARE @Guidvar CHAR(36)

SELECT TOP 1 @Guidvar = guidcol FROM #test ORDER BY id

SELECT [@Guidvar] = @Guidvar

SELECT * FROM #test WHERE guidcol = @Guidvar

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

mahalaxmi.k62 (8/1/2011)
Hi,
In my query I have to compare two fields, one uniqueidentifier and the other nvarchar.
Uniqueidentifier Field value is :
the query in Where clause is:
wpmp.grew_workproductmoduleprofilesid in
(select grew_includemodules from filteredgrew_workproductconfiguration
where
grew_workproductconfigurationid = '626cda42-128e-e011-912c-0030486321af') Now the value that the sub Query is this:
{'2632ec76-2209-e011-80d9-0030486321af','6e0cd9e9-2209-e011-80d9-0030486321af','3618be70-2409-e011-80d9-0030486321af','9e82bd76-2409-e011-80d9-0030486321af'}
On converting the uniqueidentifier to nvarchar like
CAST (grew_workproductmoduleprofilesid as nvarchar(255)) / CONVERT(nvarchar(4000),grew_workproductmoduleprofilesid)
the error does not occour but it is failing to compare the two fields and the the query does not return any value even if there is data.

It's very hard to understand what you have put into the question.

But, you can be sure that your query is not failing to compare two fields. It's not returning the values because it cannot find it based on the condition you've provided.

Could you please use the link in my signature to find out how to post your question right to get prompt and helpfull answer.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

mahalaxmi.k62 SSC Enthusiast Points: 131 More actions · Answer 3

Hi,

I understnd that following is my problem, following is the table structure

I have two tables: one 'Manager' and the other 'Reportees'. Now this manager table has an attribute called Reportees' id, the value in EACH row of this table is Exactly like this

('2632ec76-2209-e011-80d9-0030486321af','6e0cd9e9-2209-e011-80d9-0030486321af','3618be70-2409-e011-80d9-0030486321af')

now i have to match the Reportees's GUID from Reportee table and the Reportees' id from 'Manager' table.

My query is like this,

Select * from Manager

inner join Reportees

on Reportees.Reporteesid = Manager.Reporteesid

where Reportees.Reporteesid in (select Reporteesid from Manager where Managerid = 'c0a9e3d9-2909-e011-80d9-0030486321af')

Now i see that even if the Reportees.Reporteesid exists in the subquery query does not return any value.

But the same query returns value if I replace the Sub Query

(select Reporteesid from Manager where Managerid = 'c0a9e3d9-2909-e011-80d9-0030486321af') with the values of the sub query :

('2632ec76-2209-e011-80d9-0030486321af','6e0cd9e9-2209-e011-80d9-0030486321af','3618be70-2409-e011-80d9-0030486321af')

Where am i going wrong.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

PLEASE reconsider your design. You are breaking one of the most important rules of database design; normalization.

N 56°04'39.16"
E 12°55'05.25"

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

mahalaxmi.k62 (8/2/2011)
...
Where am i going wrong.

The first place where you are going wrong is in ignoring of what was advised to you.

I wil try to advise the same again:

Please provide your question in a form which allows people to help you without spending to much time on dirty work.

If you will follow the link in my signature, you will find the forum etiquete explanation.

Following it will guarantee prompt and helpfull responses.

BTW. Your query is doing exactly what you've asked it to do and it properly not returning any results. I can halp you to redesign you table (as you do brake the normalisation rules) or, if you cannot do it due to some restrictions I help you write the right query. BUT! You will need to provide what is asked from you 😉

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]