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

  • Andrew Snelling

    Mr or Mrs. 500

    Points: 509

    Hi,

    I have a SQL Server Agent Job which runs a stored procedure. In the past this has run fine, but then stopped running and started failing with the following error msg:

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

    Msg 3621, Sev 16: The statement has been terminated. [SQLSTATE 01000]

    So I have tried to run the SP in Query Analyzer to get more information and it runs OK. I have repeated this process many times and have a 100% success rate running it in QA and a 100% failure rate running it via SQL Server Agent. I have checked / changed security settings and this does not appear to make a difference.

    I have searched the web and found a number of people with the same problem but no solutions? Has anyone else experienced this issue and found a resolution (Other than running the job manually every day )

    Thanks

  • Ray M

    SSC-Insane

    Points: 21093

    That error message indicates that your trying to put a value into a table that exceeds the data type, and or length.

    For example your trying to stuff a 60 character string into a table defined with a varchar(50).

    Is the agent job calling a dts package, or doing an insert into a table that your not doing when executing the procedure manually?

     

  • Andrew Snelling

    Mr or Mrs. 500

    Points: 509

    The SP is only performing insert and update statements and is exactly the same when run in Query Analyzer.

    If it was a true truncation error would I not get the same truncation result in QA?

    At the minute I am running a trace to find out exactly which statement in the SP is causing the truncation error...takes an hour to run so hopefully will have more information soon. Once I have this I am going to run that statment in Query Analyser and then go and check the results. I have a feeling my updates are being truncated when run in QA but the error msg is not being generated.

  • Ray M

    SSC-Insane

    Points: 21093

    Wierd,

    if the same parameters are run from QA and when it was run in agent, I'd expect the same error.

    QA should capture the same error as agent.

    you still may have to go through and make sure the lenght of any data that is going to be inserted does not exceed the size of the field in the table. Its pain.

  • Andrew Snelling

    Mr or Mrs. 500

    Points: 509

    Query Analyser is not reporting the truncation error for this statement!

     insert tblDbUsageAudit (DbName,nt_username,program_name,loginame,login_time,net_address)

      select p.program_name as program_name,d.name as DbName,p.nt_username as nt_username

       ,p.loginame as loginame,p.login_time as login_time,p.net_address as net_address

      from master..sysprocesses p,master..sysdatabases d where p.spid=@@spid and p.dbid=d.dbid

    Select @@Error

    -- Table definition below...

    CREATE TABLE [tblDbUsageAudit] (

     [id] [int] IDENTITY (1, 1) NOT NULL ,

     [CreatedDate] [datetime] NULL CONSTRAINT [DF__tblDbUsag__Creat__0016B11E] DEFAULT (getdate()),

     [hostname] [varchar] (64) NULL CONSTRAINT [DF__tblDbUsag__hostn__010AD557] DEFAULT (@@servername),

     [DbName] [varchar] (64) NULL ,

     [nt_username] [varchar] (64) NULL ,

     [program_name] [varchar] (512) NULL ,

     [loginame] [varchar] (64) NULL ,

     [login_time] [varchar] (64) NULL ,

     [net_address] [varchar] (64) NULL

    ) ON [PRIMARY]

    GO

    -- DBNAme and nt_username are too small...

    Any ideas why?

  • Ken Johnson-162971

    Ten Centuries

    Points: 1296

    Hi Andrew,

    It looks like you have ansi_warnings turned off in query analyzer.

    Check the query analyzer connection properties located in the options dialogue (Tools>Options>Connection Properties tab)

  • janet macdonald-367964

    SSC-Addicted

    Points: 412

    I'm experiencing the same problem. My ANSI NULLS parameter is checked 'on'. ANy other ideas?

  • Lester Policarpio

    SSCertifiable

    Points: 7109

    As stated in the discussions please check all your column types to see if values declared are greater than of equal to the value in the table that you are inserting.

    Example:

    Common error : Column_Name is varchar(50) but you are trying to insert into that table a column whose length is 60

    "-=Still Learning=-"

    Lester Policarpio

  • janet macdonald-367964

    SSC-Addicted

    Points: 412

    I've checked the data and there is no discrepancy like that in the column length. And if I exec the script in Query Analyzer, it's just fine. I've even added ''where table.column_name is not null' wherever it was applicable in the WHERE statement. I wish the error message gave more info!

  • Big Data Man

    SSCrazy Eights

    Points: 8456

    Hi,

    Able to reproduce error and just performing some analysis and will update you shortly.

    Thanks,

    Phillip Cox

    MCITP - Database Administrator

  • Big Data Man

    SSCrazy Eights

    Points: 8456

    Hi,

    When executing via QA, it will work as the program name is set to "Microsoft SQL Server Mangement Studio - Query", but when running from SQL Server Agent the program name is similar to "SQLAgent - TSQL JobStep (Job0xxxxxxxxxxxxxxxxxx), which is to big for DBname field. Looks like the select statement mapping to the insert is incorrect, as the program field is able to store this type of string data.

    Cheers,

    Phillip Cox

    MCITP - Database Administrator

  • pjengel

    SSC Journeyman

    Points: 86

    I have a similar issue.

    I have a stored procedure which loops thru about 180 tables and inserts the data into one table used for reporting purposes. One field is a ntext field. If I execute the stored procedure from query analyzer the entire contents of the ntext field is available. When I run as scheduled task (every 1/2 hour) from sql server agent, the data in the ntext field is truncated. I do not receive an error in sql server agent - just truncated data.

  • v-ranik

    SSC Journeyman

    Points: 77

    How to tropubleshoot exactly for which column the data exceeded the defined length?

  • Stuart Mark Housden

    SSC Journeyman

    Points: 85

    I had this same problem where I always get the "truncation" message when executing stored procedures in SQL Server Agent jobs but never getting the message when I run it in Management Studio.

    After trying a number of desperate measures I commented out some "Print" statements I had and the error messages went away.

  • Mike Mullen

    Hall of Fame

    Points: 3079

    I was able to reproduce this in a SQL2000 environment. It can be fixed by changing the order of the columns selected in the first line of the select statement FROM:

    select p.program_name as program_name,d.name as DbName,p.nt_username as nt_username

    TO:

    select d.name as DbName,p.nt_username as nt_username,p.program_name as program_name

    The order of the columns in the select statement needs to match the sequence of the columns in the table.

    The original select statement executes without an error in QA but the data in the audit table is not correct. The program_name is in the DbName column, the database name is in the nt_username column, etc.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply