Delete error 8169 when uniqueidentifier NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete error 8169 when uniqueidentifier NULL

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2008-11-12 : 12:28:20

When I try to delete a record by its uniqueidentifier, DELETE works fine if the record exists, but if it doesn't (on second delete), it throws this error:

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

My query is:

DECLARE @uGIDID uniqueidentifier
SET @uGIDID = '908d474b-0172-4181-434e-9b52011ca8dc'
DELETE CN WHERE GID_ID = @uGIDID

I tried CASTing to varchar(36), using a varchar(36) variable instead of uniqueidentifier to no avail. Note that a SELECT statement with the same @uGIDID in the WHERE statement always works fine.

If I test the existence of the record first, everything is fine:

If exists(SELECT * FROM CN WHERE GID_ID = @uGIDID)
DELETE CN WHERE GID_ID = @uGIDID

but why do I have to do this? Is this a new behavior in SS2005? I switched recently from SS2000.

@@VERSION returns: Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

TIA!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 12:38:03

this might be of help

http://jesschadwick.blogspot.com/2007/11/safe-handling-of-uniqueidentifier-in.html

Marioi
Posting Yak Master

132 Posts

Posted - 2008-11-12 : 14:28:16

quote:
Originally posted by visakh16

this might be of help

http://jesschadwick.blogspot.com/2007/11/safe-handling-of-uniqueidentifier-in.html

I failed to mention that I am not entering the unique indentifier value by hand, it comes from the database, so it is always valid. The problem is that when the record exists, DELETE works, but when the record is not there, DELETE raises that error.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-12 : 14:40:42

I do not get an eror on SQL 2000, 2005 nor 2008 when trying to delete a GUID that does not exists. I just get 0 rows affect, as I would expect.

But, judging by the error message there is an error converting a string to a uniqueidentifier data type. So, there must be some conversion happening that is causing the issue.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-12 : 14:41:09

There is something else wrong, you do not need to do this

This code should run w/o error


Declare @T table(gid_id uniqueidentifier)
insert into @T 
values ('908d474b-0172-4181-434e-9b52011ca8dc')

select * from @T


DECLARE @uGIDID uniqueidentifier
SET @uGIDID = '908d474b-0172-4181-434e-9b52011ca8dc'
DELETE @t WHERE GID_ID = @uGIDID

Select * from @T

DELETE @t WHERE GID_ID = @uGIDID

Make sure you have set the DB to the correct version under options. Just putting a 2000 db on a 2005 instance does not make it a 2005 db.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Marioi
Posting Yak Master

132 Posts

Posted - 2008-11-12 : 19:31:25

Mystery solved: a delete trigger was not checking the value of the uniqueidentifier returned by SELECT GID_ID FROM deleted before using that value in a dynamic SQL query. When there was no record, GID_ID was NULL, which doesn't work well as an explicit value.

The link that visakh16 suggested turns out to be the solution (or just simple NULL checking in my case).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources