none
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
    Thursday, May 28, 2009 10:14 AM

Answers

  • 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/
    • Marked as answer by Rahul11 Saturday, May 30, 2009 10:45 AM
    Friday, May 29, 2009 5:27 AM
    Moderator

All replies

  • 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/
    Thursday, May 28, 2009 10:30 AM
    Moderator
  • 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

    • Edited by masher2 Thursday, May 28, 2009 11:58 AM
    Thursday, May 28, 2009 11:53 AM
  • 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).
    Thursday, May 28, 2009 1:59 PM
    Moderator
  • 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
    Thursday, May 28, 2009 2:07 PM
  • 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

    Friday, May 29, 2009 5:23 AM
  • 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
    Friday, May 29, 2009 5:27 AM
    Moderator
  • 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/
    • Marked as answer by Rahul11 Saturday, May 30, 2009 10:45 AM
    Friday, May 29, 2009 5:27 AM
    Moderator
  • Thanx a lot Madhu. It worked.
    Rahul Soni SQL DBA
    Friday, May 29, 2009 3:10 PM
  • 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.
    Friday, May 29, 2009 8:12 PM
    Moderator
  • Hi Madhu,

    I want a code which will send a file (output.csv) to a particular email address automatically(say xyz@email.com).
    I want to do so via job, so looking for a code stuff.

    Please help.

    Rahul Soni SQL DBA
    Monday, June 01, 2009 9:37 AM