Msg 8152, Sev 16, State 2, Line 1 : String or binary data would be truncated. [SQLSTATE 22001]

Question

Hi all, I am getting the errro "Msg 8152, Sev 16, State 2, Line 1 : String or binary data would be truncated. [SQLSTATE 22001]"---when I m trying to execute a job which is about monitoring the processes. this job calls a procedure namely "ProcessMonitor". Below is the code.Plz debug CREATE procedure ProcessMonitor as Begin Declare @maxspid int Declare @counter int declare @str1 varchar(300) declare @output varchar(600) If not exists( select 1 from sysobjects where xtype='U' and name='Mylogs') Create table Mylogs( spid int, timed_at datetime, blocked int, lastwaittype nchar(256), cpu int, physical_io bigint, memusage int, nt_username nchar(256), eventtype varchar(400), eventinfo varchar(500)) set ansi_warnings off delete from mylogs where timed_at0 Print 'Bah!' Insert into Mylogs select spid,getdate()as timed_at,blocked,lastwaittype,cpu,physical_io,memusage,nt_username, (Select eventtype from temptable), (Select eventinfo from temptable)from master..sysprocesses where spid=@counter if @@error<>0 Print 'Ugh!' --print @output Select @counter=@counter+1 End End

Answer 1

Check your create table statement. The error says you are inserting more data than what column is defined. ie . 

I doubt eventinfo and eventtype columns. Check what is the size of eventinfo in temptable and eventtype in temptable and modify the size of MyLogs table's eventtype and eventinfo columns. Rest of the columns are matching with the sysprocesses table.


Create table Mylogs( spid int, timed_at datetime, blocked int, lastwaittype nchar(256), cpu int, physical_io bigint, memusage int, nt_username nchar(256), eventtype varchar(400), eventinfo varchar(500))



Madhu

---

MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/

Answer 2

Holy obfuscated code!  Try this version and see if it works:

CREATE procedure ProcessMonitor AS
Begin
Declare @maxspid int
Declare @counter int
Declare @str1 varchar(300)
declare @output varchar(600)

If not exists  ( select 1 from sysobjects where xtype='U' and name='Mylogs')
 Create table Mylogs(
   spid int,
   timed_at datetime,
   blocked int,
   lastwaittype nchar(256),
   cpu int,
   physical_io bigint,
   memusage int,
   nt_username nchar(256),
   eventtype varchar(max),
   eventinfo varchar(max)

set ansi_warnings off
delete from mylogs where timed_at0
Print 'Bah!'
Insert into Mylogs select
  spid,
  getdate()as timed_at,
  blocked,
  lastwaittype,
  cpu,
  physical_io,
  memusage,
  nt_username,
  (Select eventtype from temptable),
  (Select eventinfo from temptable)
  from master..sysprocesses where spid=@counter

if @@error<>0
  Print 'Ugh!'
--print @output
Select @counter=@counter+1
End

(BTW, that procedure had some extra ends and other problems which makes me think you might not have pasted the entire thing.  If so, just make the change I've highlighted in bold, as Madhu suggests.)

---

Michael Asher

Answer 3

This error message means you are trying to insert a larger length field into a smaller length field.  It does NOT mean you have any data which would exceed the destination field length. 

Without knowing the source you are pulling from, I can only guess one of the char or varchar fields is not the same size.  If you want to put a varchar(1000) into a varchar(500) you need to do a LEFT(source,500).

Answer 4

Actually Tom that's not the case.  You can insert a 500-byte VARCHAR(1000) into a VARCHAR(500) field... the error is generated only when an actual overflow occurs, not when a potential one might occur.   See:

create table #big ( data varchar(1000) )
create table #small( data varchar(500) )
insert #big select 'puny'
insert into #small select * from #big

---

Michael Asher

Answer 5

Hi Michael asher, Madhu

When I am executing the code given by Michael--

I am getting the following error:

Msg 4145, Level 15, State 1, Procedure ProcessMonitor, Line 24

An expression of non-boolean type specified in a context where a condition is expected, near 'Print'.

Plz  help

Answer 6

Hi Michael asher, Madhu

When I am executing the code given by Michael--

I am getting the following error:

Msg 4145, Level 15, State 1, Procedure ProcessMonitor, Line 24

An expression of non-boolean type specified in a context where a condition is expected, near 'Print'.

Plz  help

It's the DELETE statement:

delete from mylogs where timed_at0

Michael copied it from your original post.  The WHERE clause is incomplete.

---

--Brad

Answer 7

Hi Michael asher, Madhu

When I am executing the code given by Michael--

I am getting the following error:

Msg 4145, Level 15, State 1, Procedure ProcessMonitor, Line 24

An expression of non-boolean type specified in a context where a condition is expected, near 'Print'.

Plz  help

CREATE procedure ProcessMonitor AS
Begin 
Declare @maxspid int 
Declare @counter int 
Declare @str1 varchar(300) 
declare @output varchar(600)

If not exists  ( select 1 from sysobjects where xtype='U' and name='Mylogs') 
 Create table Mylogs( 
   spid int, 
   timed_at datetime, 
   blocked int, 
   lastwaittype nchar(256), 
   cpu int, 
   physical_io bigint, 
   memusage int, 
   nt_username nchar(256), 
   eventtype varchar(max), 
   eventinfo varchar(max))

set ansi_warnings off 
delete from mylogs where timed_at=0 
Print 'Bah!' 
Insert into Mylogs select 
  spid,
  getdate()as timed_at,
  blocked,
  lastwaittype,
  cpu,
  physical_io,
  memusage,
  nt_username, 
  (Select eventtype from temptable), 
  (Select eventinfo from temptable) 
  from master..sysprocesses where spid=@counter

if @@error<>0 
  Print 'Ugh!' 
--print @output 
Select @counter=@counter+1 
End 

I made bold and underline the changes i have done. I just made the code as parsable.  This error was there in initial code you have provided i believe. Anyhow check it now

There were two problem one missing bracket and another Delete statement WHERE Condition. Check the bold and underline portion in the above code
Madhu

---

MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/

Answer 8

Thanx a lot Madhu. It worked.

---

Rahul Soni SQL DBA

Answer 9

Michael,

You are correct, that works in TSQL but not in SSIS.  In SSIS it fails if you just attempt to copy a field varchar(1000) into a varchar(500).  This is a common error in SSIS.

It not good practice to try to put 1000 char field into a 500 char field.

Answer 10

Hi Madhu,

I want a code which will send a file (output.csv) to a particular email address automatically(say [e-mail]).
I want to do so via job, so looking for a code stuff.

Please help.

---

Rahul Soni SQL DBA