Sql Server - Insufficient result space to convert uniqueidentifier value to char

Question

I am getting below error when I run sql query while copying data from one table to another,

Msg 8170, Level 16, State 2, Line 2 Insufficient result space to convert uniqueidentifier value to char.

My sql query is,

INSERT INTO dbo.cust_info (
uid,
first_name,
last_name
)
SELECT
NEWID(),
first_name,
last_name
FROM dbo.tmp_cust_info

My create table scripts are,

CREATE TABLE [dbo].[cust_info](
    [uid] [varchar](32) NOT NULL,
    [first_name] [varchar](100) NULL,
    [last_name] [varchar](100) NULL)

CREATE TABLE [dbo].[tmp_cust_info](
    [first_name] [varchar](100) NULL,
    [last_name] [varchar](100) NULL)

I am sure there is some problem with NEWID(), if i take out and replace it with some string it is working.

I appreciate any help. Thanks in advance.

Answer 1

A guid needs 36 characters (because of the dashes). You only provide a 32 character column. Not enough, hence the error.

Answer 2

You need to use one of 3 alternatives

1, A uniqueidentifier column, which stores it internally as 16 bytes. When you select from this column, it automatically renders it for display using the 8-4-4-4-12 format.

CREATE TABLE [dbo].[cust_info](
    [uid] uniqueidentifier NOT NULL,
    [first_name] [varchar](100) NULL,
    [last_name] [varchar](100) NULL)

2, not recommended Change the field to char(36) so that it fits the format, including dashes.

CREATE TABLE [dbo].[cust_info](
    [uid] char(36) NOT NULL,
    [first_name] [varchar](100) NULL,
    [last_name] [varchar](100) NULL)

3, not recommended Store it without the dashes, as just the 32-character components

INSERT INTO dbo.cust_info (
uid,
first_name,
last_name
)
SELECT
replace(NEWID(),'-',''),
first_name,
last_name
FROM dbo.tmp_cust_info

Answer 3

I received this error when I was trying to perform simple string concatenation on the GUID. Apparently a VARCHAR is not big enough.

I had to change:

SET @foo = 'Old GUID: {' + CONVERT(VARCHAR, @guid) + '}';

to:

SET @foo = 'Old GUID: {' + CONVERT(NVARCHAR(36), @guid) + '}';

...and all was good. Huge thanks to the prior answers on this one!

Answer 4

Increase length of your uid column from varchar(32) ->varchar(36) because guid take 36 characters Guid.NewGuid().ToString() -> 36 characters outputs: 12345678-1234-1234-1234-123456789abc

Answer 5

You can try this. This worked for me.

Specify a length for VARCHAR when you cast/convert a value..for uniqueidentifier use VARCHAR(36) as below:

SELECT Convert (varchar(36),NEWID()) AS NEWID

The default length for VARCHAR datatype if we don't specify a length during CAST/CONVERT is 30..

Credit : Krishnakumar S

Reference : https://social.msdn.microsoft.com/Forums/en-US/fb24a153-f468-4e18-afb8-60ce90b55234/insufficient-result-space-to-convert-uniqueidentifier-value-to-char?forum=transactsql