SQL error Msg 8152, Level 16, State 13, Line 1

Hello,

i get an SQL error form my SQL statement and ot sure where the error is it sounds like its inserting a string into Bianary


INSERT INTO [ITDEdb].[dbo].[dbt_backup_course]
([course_ID]
,[department]
,[prefix]
,[course_number]
,[short_title]
,[SCH_value]
,[level_value]
,[lec_lab_hours]
,[multiple_topics]
,[degree_programs]
,[maj_min_support]
,[required_or_elective]
,[deleted_course]
,[full_title]
,[description]
,[prerequisites]
,[justification]
,[new_prefix]
,[new_course_number]
,[new_short_title]
,[identify_changes]
,[reason_for_deleting]
,[cross_list_with]
,[begin_date]
,[submitted_by]
,[form]
,[ClassInfoTimeStamp]
,[process_level]
,[CIP_number]
,[year])
select
[course_ID]
,[department]
,[prefix]
,[course_number]
,[short_title]
,[SCH_value]
,[level_value]
,[lec_lab_hours]
,[multiple_topics]
,[degree_programs]
,[maj_min_support]
,[required_or_elective]
,[deleted_course]
,[full_title]
,[description]
,[prerequisites]
,[justification]
,[new_prefix]
,[new_course_number]
,[new_short_title]
,[identify_changes]
,[reason_for_deleting]
,[cross_list_with]
,[begin_date]
,[submitted_by]
,[form]
,[ClassInfoTimeStamp]
,[process_level]
,[CIP_number]
,[year]
from [ITDEDEV].[dbo].[dbt_course_2012]

Open in new window




here is the table struct
USE [ITDEDEV]
GO

/****** Object:  Table [dbo].[dbt_course_2012]    Script Date: 08/27/2012 12:06:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dbt_course_2012](
	[course_ID] [int] IDENTITY(1,1) NOT NULL,
	[department] [int] NULL,
	[prefix] [nvarchar](50) NULL,
	[course_number] [int] NULL,
	[short_title] [nvarchar](100) NULL,
	[SCH_value] [nvarchar](255) NULL,
	[level_value] [nvarchar](255) NULL,
	[lec_lab_hours] [nvarchar](255) NULL,
	[multiple_topics] [nvarchar](255) NULL,
	[degree_programs] [nvarchar](255) NULL,
	[maj_min_support] [nvarchar](255) NULL,
	[required_or_elective] [nvarchar](255) NULL,
	[deleted_course] [nvarchar](100) NULL,
	[full_title] [nvarchar](100) NULL,
	[description] [nvarchar](2000) NULL,
	[prerequisites] [nvarchar](1000) NULL,
	[justification] [nvarchar](4000) NULL,
	[new_prefix] [nvarchar](50) NULL,
	[new_course_number] [int] NULL,
	[new_short_title] [nvarchar](100) NULL,
	[identify_changes] [nvarchar](1000) NULL,
	[reason_for_deleting] [nvarchar](1000) NULL,
	[cross_list_with] [nvarchar](1000) NULL,
	[begin_date] [int] NULL,
	[submitted_by] [int] NULL,
	[form] [int] NULL,
	[ClassInfoTimeStamp] [smalldatetime] NULL,
	[process_level] [int] NULL,
	[CIP_number] [float] NULL,
	[year] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

Open in new window

LVL 1
Chris JonesLead Application Web DeveloperAsked:
Who is Participating?
 
stalhwConnect With a Mentor Commented:
like blingtec said justification is different:



CREATE TABLE [dbo].[dbt_backup_course](
*...*      
      [justification] [nvarchar](2000) NULL,
VERSUS
CREATE TABLE [dbo].[dbt_course_2012](
*...*
      [justification] [nvarchar](4000) NULL,
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>i get an SQL error form my SQL statement
Copy-pasting the SQL error message into this question would be an excellent start.

>it sounds like its inserting a string into Bianary
Error messages display in text.

btw when you have a bunch of columns in a SQL statement, it's usually good practice to (1)  include a carriage return every 5th column, or (2) have five columns on a single line, ending in a carriage return.

That way it becomes obvious if your INSERT and SELECT clauses have a different number of columns, and it makes it easy to troubleshoot using {ctrl}- K , {ctrl}-C to comment out lines, then {ctrl}- K , {ctrl}-U to uncomment.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
ok i will update the statement and re submit
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Chris JonesLead Application Web DeveloperAuthor Commented:
INSERT INTO [ITDEdb].[dbo].[dbt_backup_course]
([course_ID]
,[department]
,[prefix]
,[course_number]
,[short_title]

,[SCH_value]
,[level_value]
,[lec_lab_hours]
,[multiple_topics]
,[degree_programs]

,[maj_min_support]
,[required_or_elective]
,[deleted_course]
,[full_title]
,[description]

,[prerequisites]
,[justification]
,[new_prefix]
,[new_course_number]
,[new_short_title]

,[identify_changes]
,[reason_for_deleting]
,[cross_list_with]
,[begin_date]
,[submitted_by]

,[form]
,[ClassInfoTimeStamp]
,[process_level]
,[CIP_number]
,[year])

select
[course_ID]
,[department]
,[prefix]
,[course_number]
,[short_title]

,[SCH_value]
,[level_value]
,[lec_lab_hours]
,[multiple_topics]
,[degree_programs]

,[maj_min_support]
,[required_or_elective]
,[deleted_course]
,[full_title]
,[description]

,[prerequisites]
,[justification]
,[new_prefix]
,[new_course_number]
,[new_short_title]

,[identify_changes]
,[reason_for_deleting]
,[cross_list_with]
,[begin_date]
,[submitted_by]

,[form]
,[ClassInfoTimeStamp]
,[process_level]
,[CIP_number]
,[year]

from [ITDEDEV].[dbo].[dbt_course_2012]
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
... and this returns what error message?  Mind reading is not one of my stronger suits ...
0
 
stalhwCommented:
Could you show the CREATE Table for your backup table?
Maybe you left the identity statement in the Table backup?

But the probable reason is that some field in your backup table is defined shorter thant in the real table.
For example if in the real table:
[prefix] [nvarchar](50) NULL
and in the backup:
[prefix] [nvarchar](32) NULL

If a prefix from the real table is longer than 32, you will get that kind of SQL error 8152
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
Error

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
backup table

USE [ITDEdb]
GO

/****** Object:  Table [dbo].[dbt_backup_course]    Script Date: 08/27/2012 13:11:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dbt_backup_course](
	[course_ID] [int] NOT NULL,
	[department] [int] NULL,
	[prefix] [nvarchar](50) NULL,
	[course_number] [int] NULL,
	[short_title] [nvarchar](50) NULL,
	[SCH_value] [nvarchar](255) NULL,
	[level_value] [nvarchar](255) NULL,
	[lec_lab_hours] [nvarchar](255) NULL,
	[CIP_number] [nvarchar](255) NULL,
	[multiple_topics] [nvarchar](255) NULL,
	[degree_programs] [nvarchar](255) NULL,
	[maj_min_support] [nvarchar](255) NULL,
	[required_or_elective] [nvarchar](255) NULL,
	[deleted_course] [nvarchar](100) NULL,
	[full_title] [nvarchar](100) NULL,
	[description] [nvarchar](2000) NULL,
	[prerequisites] [nvarchar](1000) NULL,
	[justification] [nvarchar](2000) NULL,
	[new_prefix] [nvarchar](50) NULL,
	[new_course_number] [int] NULL,
	[new_short_title] [nvarchar](50) NULL,
	[identify_changes] [nvarchar](1000) NULL,
	[reason_for_deleting] [nvarchar](1000) NULL,
	[cross_list_with] [nvarchar](1000) NULL,
	[begin_date] [int] NULL,
	[submitted_by] [int] NULL,
	[form] [int] NULL,
	[ClassInfoTimeStamp] [smalldatetime] NULL,
	[process_level] [int] NULL,
	[year] [int] NULL
) ON [PRIMARY]

GO

Open in new window

0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
good call  talhw

it looks liek this line may be off

      [justification] [nvarchar](4000) NULL,
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
but the design view shows the correct number ?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>String or binary data would be truncated.
This is a warning message, as stalhw commented above, that you are attempting to SELECT a column of a certain char/varchar size (say 100), where the column you are INSERTing into is smaller (say 50).

I'd review all the character columns in dbt_course_2012, and compare to dbt_backup_course, to see if any of the data types have fewer characters.
0
 
stalhwConnect With a Mentor Commented:
After verification, justification is not the only one...

[short_title] [nvarchar](50) NULL, // [short_title] [nvarchar](100) NULL,
[justification] [nvarchar](2000) NULL, // [justification] [nvarchar](4000) NULL,
[new_short_title] [nvarchar](50) NULL, // [new_short_title] [nvarchar](100) NULL,

also your backup table has a different type for CIP_number:

[CIP_number] [nvarchar](255) NULL, // [CIP_number] [float] NULL,
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
wow this si a legacy application that i am trying to fix and the system is crazy i did a copy from the table and it created diffrent types.
0
 
stalhwConnect With a Mentor Commented:
You may want to look at other options...
Like simply:
SELECT * INTO [ITDEdb].[dbo].[dbt_backup_course_NEW] FROM [ITDEDEV].[dbo].[dbt_course_2012] 

Open in new window


SELECT * INTO NewTable FROM OldTable
Will copy your current table, data and structure... into a new table that will be created automatically.

So I dunno if your are emptying your backup table before your insert, if you are, then instead drop the table, and do a SELECT * INTO, it will create the table with an exact copy of the current table.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
the only thing is that the data in the backup table i cant drop it.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
here is teh table i did th initioal copy from

USE [ITDEdb]
GO

/****** Object:  Table [dbo].[dbt_course]    Script Date: 08/27/2012 14:01:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dbt_course](
	[course_ID] [int] IDENTITY(1,1) NOT NULL,
	[department] [int] NULL,
	[prefix] [nvarchar](50) NULL,
	[course_number] [int] NULL,
	[short_title] [nvarchar](100) NULL,
	[SCH_value] [nvarchar](255) NULL,
	[level_value] [nvarchar](255) NULL,
	[lec_lab_hours] [nvarchar](255) NULL,
	[multiple_topics] [nvarchar](255) NULL,
	[degree_programs] [nvarchar](255) NULL,
	[maj_min_support] [nvarchar](255) NULL,
	[required_or_elective] [nvarchar](255) NULL,
	[deleted_course] [nvarchar](100) NULL,
	[full_title] [nvarchar](100) NULL,
	[description] [nvarchar](2000) NULL,
	[prerequisites] [nvarchar](1000) NULL,
	[justification] [nvarchar](4000) NULL,
	[new_prefix] [nvarchar](50) NULL,
	[new_course_number] [int] NULL,
	[new_short_title] [nvarchar](100) NULL,
	[identify_changes] [nvarchar](1000) NULL,
	[reason_for_deleting] [nvarchar](1000) NULL,
	[cross_list_with] [nvarchar](1000) NULL,
	[begin_date] [int] NULL,
	[submitted_by] [int] NULL,
	[form] [int] NULL,
	[ClassInfoTimeStamp] [smalldatetime] NULL,
	[process_level] [int] NULL,
	[CIP_number] [float] NULL,
 CONSTRAINT [PK_dbt_course] PRIMARY KEY CLUSTERED 
(
	[course_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[dbt_course] ADD  CONSTRAINT [DF_dbt_course_ClassInfoTimeStamp]  DEFAULT (getdate()) FOR [ClassInfoTimeStamp]
GO

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>i did a copy from the table and it created diffrent types.
This is highly unlikely, unless the copy failed, or if it was copied into another database application that may have changed the data types.  

>the only thing is that the data in the backup table i cant drop it.
Explain this, and if it's becuase of your company and priveleges you'll want to impart some wisdom on somebody that a backup table isn't too useful if it isn't an exact match of the source of its data.

It looks like the original question has been answered.
0
 
Chris JonesLead Application Web DeveloperAuthor Commented:
thanks for all of the help
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Dude ... really?
0
 
stalhwCommented:
How often are you doing backups?
You could always do a SELECT * INTO backup_course_2012_08 FROM course
And create a new table with a name that includes part of the date...

What probably happened, is that the backup table was created for version 1 of the software, and since then it has been updated, some fields were extended, and a varchar became float...

You can always alter your current backup table to extend the fields, and change the CIP_number datatype, I just hope all the old values can be converted to float...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.