Why won't this SQL CAST work?

Question

I have a nvarchar(50) column in a SQL Server 2000 table defined as follows:

TaskID nvarchar(50) NULL

I need to populate this column with random SQL GUID's using the NEWID() function (I am unable to change the column type to uniqueidentifier).

I tried this:

UPDATE TaskData SET TaskID = CAST(NEWID() AS nvarchar)

but I got the following error:

Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type nvarchar.

I also tried:

UPDATE TaskData SET TaskID = CAST(NEWID() AS nvarchar(50))

but then got this error:

Msg 8152, Level 16, State 6, Line 1 String or binary data would be truncated.

I don't understand why this doesn't work but this does:

DECLARE @TaskID nvarchar(50)
SET @TaskID = CAST(NEW() AS nvarchar(50))

I also tried CONVERT(nvarchar, NEWID()) and CONVERT(nvarchar(50), NEWID()) but got the same errors.

Update:

Ok, my eyesight is going, the column size on the table is nvarchar(32) not 50. Deepest apologies for the timewasting and thanks for all the answers.

Solution 1

This test script works fine for me... I can only suggest that maybe your TaskId isn't an NVARCHAR(50) like you say? Try an sp_columns just to check...

CREATE Table #TaskData (TaskId NVARCHAR(50))
INSERT INTO #TaskData (TaskId) SELECT CONVERT(NVARCHAR(50), NEWID())
UPDATE #TaskData SET TaskId = CONVERT(NVARCHAR(50), NEWID())
DROP TABLE #TaskData

Solution 2

When you do not specify the size of your varchar/nvarchar during a cast or convert, it defaults to 30 characters. You need 36 characters to convert a guid to a string. That is why you get the error.

Either of these will work:

Select Cast(NewId() as nvarchar(36)), CONVERT(nvarchar(36), NEWID())

Solution 3

Please try the following cast:

CAST(NEWID() AS varchar(255))

Comments

[Andy White]

This seems to me to be the most accurate answer here.