String or binary data would be truncated

Question

First to clarify, I have already done a search and found a few identical questions, but the answers did not solve my problem.

I have this code:

    SET IDENTITY_INSERT [STUDY] ON
    INSERT INTO [STUDY]
    ([STUDY_ID],[PARENT_ID],[GROUP_ID],[WORKFLOW_NODE_ID],[STUDY_TEMPLATE_ID]
           ,[INSPECTION_PLAN_ID],[NAME],[DESCRIPTION],[STATUS],[OLD_STATUS],[CREATED_ON],[COMPLETED_ON]
           ,[AUTHORIZED_ON],[EVENTS],[NEEDS_REVIEW],[HAS_NOTES],[HAS_AUDITS],[STUDY_PART],[STUDY_TYPE]
           ,[EXTERNAL_REFERENCE],[CREATED_BY],[COMPLETED_BY],[AUTHORISED_BY],[ARCHIVED_CHILD_COMPLETE])
    VALUES (12345,null,null,1234,12,null,'asdf / asdf','blahblahblah','A','VPC','2010-07-01','2010-08-05','2010-09-04','(asdf,1234,#1234,F,T)','F',null,null,'F',null,'1234',1234,1234,1234,'F')
    SET IDENTITY_INSERT [STUDY] OFF

And when i run it, I get this error:

Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

Here's the create script for STUDY:

CREATE TABLE [dbo].[STUDY]
(
    [STUDY_ID] INT IDENTITY(1,1) NOT NULL,
    [PARENT_ID] BIGINT,
    [GROUP_ID] BIGINT,
    [WORKFLOW_NODE_ID] BIGINT,
    [STUDY_TEMPLATE_ID] BIGINT,
    [INSPECTION_PLAN_ID] BIGINT,
    [NAME] VARCHAR(255),
    [DESCRIPTION] VARCHAR(4000),
    [STATUS] VARCHAR(5),
    [OLD_STATUS] VARCHAR(5),
    [CREATED_ON] DATE,
    [COMPLETED_ON] DATE,
    [AUTHORIZED_ON] DATE,
    [EVENTS] VARCHAR(255),
    [NEEDS_REVIEW] VARCHAR(1),
    [HAS_NOTES] VARCHAR(1),
    [HAS_AUDITS] VARCHAR(1),
    [STUDY_PART] VARCHAR(1),
    [STUDY_TYPE] VARCHAR(255),
    [EXTERNAL_REFERENCE] VARCHAR,
    [CREATED_BY] BIGINT,
    [COMPLETED_BY] BIGINT,
    [AUTHORISED_BY] BIGINT,
    [ARCHIVED_CHILD_COMPLETE] VARCHAR(1)

PRIMARY KEY CLUSTERED 
(
    [STUDY_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

+1 for providing the scripts and the query.

Quassnoi
– Quassnoi

2010-11-10 14:15:45 +00:00
Commented Nov 10, 2010 at 14:15 — Quassnoi
– Quassnoi, Commented Nov 10, 2010 at 14:15

Quassnoi · Accepted Answer · 2010-11-10 14:14:31Z

7

EXTERNAL_REFERENCE is declared as VARCHAR which is treated as VARCHAR(1).

You are trying to insert '1234' into this field.

answered Nov 10, 2010 at 14:14

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Entity Over a year ago

Oh, thanks man! I was bashing my head on the wall over this for quite a while.

Peter M · Accepted Answer · 2010-11-10 14:15:56Z

1

Assuming I am counting fields correctly, you have defined "[EXTERNAL_REFERENCE] VARCHAR," which has a default length of 1 char, but the data you supply it is '1234' - 4 chars

answered Nov 10, 2010 at 14:15

Peter M

7,5913 gold badges55 silver badges97 bronze badges

Collectives™ on Stack Overflow

String or binary data would be truncated

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related