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.

Variant

Answer 1

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

Brakesman

And btw, you should use uniqueidentifier column type to store guids, not character(36) (var is no needed since is not variable, is it?). uniqueidentifier only needs 16 bytes for storage, as opposed to 36 needed for the character representation of a guid. – Brakesman

simple yet straight forward answer. – Yongyoni

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

Laggard

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!

Legendary

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

Chardin

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

Redan

This resolved my issue, I was attempting pass a @TraceId parameter into SPROCS to enable information logging if actions were performed without error. Once I updated to varchar(36) my issue was resolved, however initially I had forgotten to update the top level paremeter @TraceId output to varchar(36) as well so it truncated the value initially in my logs. Don't forget to update all the references or you may have unexpected results lol. – Soupy