[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1552
  • Last Modified:

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

0
Chris Jones
Asked:
Chris Jones
  • 10
  • 5
  • 5
3 Solutions
 
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 JonesAuthor Commented:
ok i will update the statement and re submit
0
 
Chris JonesAuthor 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 JonesAuthor Commented:
Error

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.
0
 
Chris JonesAuthor 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 JonesAuthor Commented:
good call  talhw

it looks liek this line may be off

      [justification] [nvarchar](4000) NULL,
0
 
Chris JonesAuthor 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
 
stalhwCommented:
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
 
stalhwCommented:
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 JonesAuthor 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
 
stalhwCommented:
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 JonesAuthor Commented:
the only thing is that the data in the backup table i cant drop it.
0
 
Chris JonesAuthor 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 JonesAuthor 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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 10
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now