Conversion failed when converting from a character string to uniqueidentifier.

[moondaddy]

Using sql 08 I need to convert a string to a uniqueidentifer which is
failing. Please advise how I can do this convertion. Thanks.

DECLARE
@id uniqueidentifier,
@str varchar;

-- I tried both guids below and both failed.
--SET @str = '0be3cf11-7daf-4f61-bca2-9797293a79f4' --this was created in
.net
SET @str = 'de8dcc34-a07f-4fb5-a430-b92733adc725' --this was created in sql
using newid()

SET @id = CONVERT(UNIQUEIDENTIFIER, @str);

select @id

and this code returns this error msg:
Conversion failed when converting from a character string to
uniqueidentifier.

[Erland Sommarskog]

Post by moondaddy
Using sql 08 I need to convert a string to a uniqueidentifer which is
failing. Please advise how I can do this convertion. Thanks.
DECLARE
@id uniqueidentifier,
@str varchar;
-- I tried both guids below and both failed.
.net
using newid()
Conversion failed when converting from a character string to
uniqueidentifier.

Try "SELECT @str". That will give you a revelation.

[moondaddy]

Thanks for the reply, but that had not effect on the outcome. I stil get
the error.

Post by Erland Sommarskog

Post by moondaddy
Using sql 08 I need to convert a string to a uniqueidentifer which is
failing. Please advise how I can do this convertion. Thanks.
DECLARE
@id uniqueidentifier,
@str varchar;
-- I tried both guids below and both failed.
.net
using newid()
Conversion failed when converting from a character string to
uniqueidentifier.

--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jeffrey Williams]

Post by moondaddy
Thanks for the reply, but that had not effect on the outcome. I stil get
the error.

Post by Erland Sommarskog

Post by moondaddy
Using sql 08 I need to convert a string to a uniqueidentifer which is
failing. Please advise how I can do this convertion. Thanks.
DECLARE
@id uniqueidentifier,
@str varchar;
-- I tried both guids below and both failed.
.net
using newid()
Conversion failed when converting from a character string to
uniqueidentifier.

--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

what was the result when you performed SELECT @str?

The revelation you should have had - if you ran the above is that
declaring a varchar without a length uses a default length of 1.

Jeff

[moondaddy]

Thanks that was it. I set the varchar to varchar(50) and it worked.

Post by Jeffrey Williams

Post by moondaddy
Thanks for the reply, but that had not effect on the outcome. I stil get
the error.

Post by Erland Sommarskog

Post by moondaddy
Using sql 08 I need to convert a string to a uniqueidentifer which is
failing. Please advise how I can do this convertion. Thanks.
DECLARE
@id uniqueidentifier,
@str varchar;
-- I tried both guids below and both failed.
.net
in
sql
using newid()
Conversion failed when converting from a character string to
uniqueidentifier.

--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

The revelation you should have had - if you ran the above is that
declaring a varchar without a length uses a default length of 1.
Jeff

[moondaddy]

Actually, I'm still having trouble. Here's exactly what I have:

DECLARE
@tmpSTR varchar(50)

-- run code...

CREATE TABLE #Ap_Ids (ID uniqueidentifier)

INSERT INTO #Ap_Ids (ID)
VALUES (CONVERT(UNIQUEIDENTIFIER, @tmpSTR))

It errors on the insert statement and when I step through this code here's
the value of @tmpSTR in the watch window:

@tmpSTR '0be3cf11-7daf-4f61-bca2-9797293a79f4' varchar

so it seems that I'm doing everything correctly, but I'm still getting the
error.

any more ideas?

Post by Jeffrey Williams

Post by moondaddy
Thanks for the reply, but that had not effect on the outcome. I stil get
the error.

Post by Erland Sommarskog

Post by moondaddy
Using sql 08 I need to convert a string to a uniqueidentifer which is
failing. Please advise how I can do this convertion. Thanks.
DECLARE
@id uniqueidentifier,
@str varchar;
-- I tried both guids below and both failed.
.net
in
sql
using newid()
Conversion failed when converting from a character string to
uniqueidentifier.

--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

The revelation you should have had - if you ran the above is that
declaring a varchar without a length uses a default length of 1.
Jeff

[Erland Sommarskog]

Post by moondaddy
DECLARE
@tmpSTR varchar(50)
-- run code...
CREATE TABLE #Ap_Ids (ID uniqueidentifier)
INSERT INTO #Ap_Ids (ID)
It errors on the insert statement and when I step through this code here's
@tmpSTR '0be3cf11-7daf-4f61-bca2-9797293a79f4' varchar
so it seems that I'm doing everything correctly, but I'm still getting the
error.
any more ideas?

Yes, I have an idea: post exactly what you are doing, and also post the
error message you get. Then we don't have to guess.

For the record, this runs without error on my machine:

DECLARE @tmpSTR varchar(50)
SET @tmpSTR = '0be3cf11-7daf-4f61-bca2-9797293a79f4'
CREATE TABLE #Ap_Ids (ID uniqueidentifier)
INSERT INTO #Ap_Ids (ID)
VALUES (CONVERT(UNIQUEIDENTIFIER, @tmpSTR))
DROP TABLE #Ap_Ids

[Tibor Karaszi]

The values you have converts just fine for me, so there is probably
something else:

DECLARE @tmpSTR varchar(50) = '0be3cf11-7daf-4f61-bca2-9797293a79f4'

CREATE TABLE #Ap_Ids (ID uniqueidentifier)

INSERT INTO #Ap_Ids (ID)
VALUES (CONVERT(UNIQUEIDENTIFIER, @tmpSTR))
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by moondaddy
DECLARE
@tmpSTR varchar(50)
-- run code...
CREATE TABLE #Ap_Ids (ID uniqueidentifier)
INSERT INTO #Ap_Ids (ID)
It errors on the insert statement and when I step through this code
@tmpSTR '0be3cf11-7daf-4f61-bca2-9797293a79f4' varchar
so it seems that I'm doing everything correctly, but I'm still
getting the error.
any more ideas?

Post by Jeffrey Williams

Post by moondaddy
Thanks for the reply, but that had not effect on the outcome. I
stil get the error.

Post by Erland Sommarskog

Post by moondaddy
Using sql 08 I need to convert a string to a uniqueidentifer which is
failing. Please advise how I can do this convertion. Thanks.
DECLARE
@id uniqueidentifier,
@str varchar;
-- I tried both guids below and both failed.
.net
created in
sql
using newid()
Conversion failed when converting from a character string to
uniqueidentifier.

--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

The revelation you should have had - if you ran the above is that
declaring a varchar without a length uses a default length of 1.
Jeff

[Erland Sommarskog]

Post by moondaddy
Thanks for the reply, but that had not effect on the outcome. I stil get
the error.

I forgor to mention that you should actually look at the output.

[John Paul Cook]

I've seen this error a few times with SQL Server 2008. It's still happening
at times. Searching reveals other people have also experienced this problem.
I'm not dismissing it as a coding error because I have perfectly valid syntax.

The first time I saw it was when executing statements like this:

delete from something where aguid = '230BD1E4-FD9B-4A09-B666-BF4B7BD8F79D'

I changed it to this and it worked:

delete from something where aguid =
CAST('230BD1E4-FD9B-4A09-B666-BF4B7BD8F79D' as uniqueidentifier)

The last time I saw it was when executing 100s of pairs of deletes and
inserts that look something like this:

delete from something where aguid = '230BD1E4-FD9B-4A09-B666-BF4B7BD8F79D'
insert into something (aguid, ...) values
('230BD1E4-FD9B-4A09-B666-BF4B7BD8F79D' , ...)

The curious thing is that 100s of pairs of statements worked fine, then all
of a sudden, the same syntax threw an exception. I have a repeatable test
case.

In every case I've seen, an explicit transaction was involved and the
transactions were on the order of at least a few seconds or more.

If I take the offending statement and execute it by itself, it works just
fine.

By the way, CAST is ANSI, CONVERT is SQL Server specific, which is why I
chose CAST for my workaround.

John Cook
MVP Virtual Machines: Development

[John Paul Cook]

I went back and checked the code for the second test case. I agreed with
Erland, Jeffrey, and Tibor that some type of syntax was the most likely
explanation. Somewhere in the 1000s of lines of code spewed out by a code
generator was a string that wasn't a valid guid. I hadn't seen any problems
from the code generator in the past, so I was mistaken in being too quick to
trust it.