Sql Server - Insufficient Result Space to Convert Uniqueidentifier Value to Char
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...
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.
5 Answers
A guid needs 36 characters (because of the dashes). You only provide a 32 character column. Not enough, hence the error.
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
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!
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
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 :
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
