Why is this SQL CAST not working?

Question

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

 TaskID nvarchar(50) NULL 

I need to populate this column with a random SQL identifier using the NEWID() function (I cannot 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 conversion expression for nvarchar data type.

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 will be Truncated.

I do not understand why this does not work, but it 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:

Well, my vision is on, the column size on the nvarchar(32) table is not 50. My deepest apologies for the timewasting and thanks for all the answers.

Answer 1

This test script works fine for me ... I can only assume that maybe your TaskId is not NVARCHAR (50), as you say? Try 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 

Answer 2

If you do not specify the size of your varchar / nvarchar during translation or conversion, by default it will be 30 characters. It takes 36 characters to convert a pointer to a string. That is why you get an error.

Any of them will work:

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

Answer 3

Try the following:

 CAST(NEWID() AS varchar(255)) 

Answer 4

use varchar data type, nvarchar needs double size