cast uniqueidentifier to varchar? not working!

[Gekkomna]

Hi all,

From what I understand from SQL help online, convert (or cast) are
meant to change the datatype of a value to another datatype. However,
in this case neither of them seem to work! I've been staring at this
line for the past few hours, and cant even begin to imagine what im
doing wrong!

ObjID is of type UniqueIdentifier.
@OID is a varchar(255) which is either '0' or a UniqueIdentifier as a
string.
PID is varchar(255).

This is what im trying to run...
select PID from tblDirectoryObject where ObjID=
(CONVERT(uniqueidentifier, @OID))

And this is the error message:
Server: Msg 8169, Level 16, State 2, Line 4
Syntax error converting from a character string to uniqueidentifier.

Whats wrong?! Please help?!

Thanks in advance

[Tony Hadfield]

I don't know if this helps, but here is what I noticed in the online help...
From the example in the online help, it looks like you are trying to cast to a uniqueidentifier instead of casting to a string.

select PID from tblDirectoryObject where @OID=
(CONVERT(varchar(255), ObjID))

here is the online help I was refering to...

When using either CAST or CONVERT, two pieces of information are required:

The expression to convert (for example, the sales report requires the sales data to be converted from monetary data to character data).

The data type to convert the given expression to, for example, varchar or any other SQL Server-supplied data type.
Unless you store the converted value, a conversion is valid only for the length of the CAST or CONVERT function.

This example uses CAST in the first SELECT statement and CONVERT in the second SELECT statement to convert the title column to a char(50) column, to make the results more readable:

USE pubs
SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'

Or

USE pubs
SELECT CONVERT(char(50), title), ytd_sales
FROM titles
WHERE type = 'trad_cook'

Here is the result set: (for either query)

----- Gekkomna wrote: -----

[Louis Davidson]

You do realize that uniqueidentifier are not just a random number of
characters, right? A unique identifier must be a legal unique identifier.
Also, no need to store them in a 255 character string, 36 is enough:

declare @holdId uniqueidentifier
set @holdId = '6F93D519-5BE9-4006-8792-2EE03A5BF5CA'

select cast(@holdId as varchar(36))

----------------------------------------
6F93D519-5BE9-4006-8792-2EE03A5BF5CA

now, just a string formatted

set @holdId = 'jfjwlee2-asdf-adfa-asff-afddsafasdff'

Server: Msg 8169, Level 16, State 2, Line 8

Syntax error converting from a character string to uniqueidentifier.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr...@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Gekkomna" <mna...@yahoo.co.uk> wrote in message
news:4df18b1d.04020...@posting.google.com...