Msg 8152, Sev 16: String or binary data would be truncated.

  • I know that there is a thread opened under SQL Server Agent but there was really no end result in that thread.  I have a similar problem, a SQL Agent Job that runs this SP, exec Prc_GetDBList, which calls another SP, Prc_DBDataImport, and that one calls the 3rd SP, Prc_PostDataImportValidation. 

    How this job works is this:

    1.  Find the DB that is qualified to be imported into the reporting server and run a backup and restore of that DB as TempSurvey DB.  2.  Import data from this TempSurvey DB with Prc_ DBDataImport.  3.  Validate data import, and upon success commit tran in the previous step, else rollback tran.  4.  If 3 is successful, delete the TempSurvey DB and restart from step 1 again. 

    The job used to run beautifully without error, then a month and a half ago it was giving this error each night the job runs in SQL Server Agent:

    (Message 0)  Prc_DataDefinitionValidation : s123456 [SQLSTATE 01000] (Message 0)  Prc_DBDataImport : s123456 [SQLSTATE 01000] (Message 0)  Prc_PostDataImportValidation : s123456 [SQLSTATE 01000] (Message 0)  Deleting database file 'I:\Temp\TempSurveyLog\tempsurvey.ldf'. [SQLSTATE 01000] (Message 3738)  Deleting da...  The step failed. 

    In the output file from SQL Server Agent job, it gives this message:

    Deleting database file 'I:\Temp\TempSurveyLog\tempsurvey.ldf'. [SQLSTATE 01000]

    Deleting database file 'I:\Temp\TempSurveyData\tempsurvey.mdf'. [SQLSTATE 01000]

    Processed 2256 pages for database 'TempSurvey', file 's123455_dat' on file 1. [SQLSTATE 01000]Processed 1 pages for database 'TempSurvey', file 's123455_log' on file 1. [SQLSTATE 01000]

    RESTORE DATABASE successfully processed 2257 pages in 0.208 seconds (88.856 MB/sec). [SQLSTATE 01000]

    ===================================================== [SQLSTATE 01000]

    Prc_DataDefinitionValidation : s123455 [SQLSTATE 01000]

    Prc_DBDataImport              : s123455 [SQLSTATE 01000]

    Prc_PostDataImportValidation : s123455 [SQLSTATE 01000]

    output                                                                                                                                                                                                                                                         ----------------------------------------------------------------------------------------------------------(null)

    Deleting database file 'I:\Temp\TempSurveyLog\tempsurvey.ldf'. [SQLSTATE 01000]

    Deleting database file 'I:\Temp\TempSurveyData\tempsurvey.mdf'. [SQLSTATE 01000]

    Processed 1256 pages for database 'TempSurvey', file 's123456_dat' on file 1. [SQLSTATE 01000]

    Processed 1 pages for database 'TempSurvey', file 's123456_log' on file 1. [SQLSTATE 01000]

    RESTORE DATABASE successfully processed 1257 pages in 0.122 seconds (84.345 MB/sec). [SQLSTATE 01000]

    ===================================================== [SQLSTATE 01000]

    Prc_DataDefinitionValidation : s123456 [SQLSTATE 01000]

    Prc_DBDataImport              : s123456 [SQLSTATE 01000]

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

    Msg 0, Sev 16: Prc_PostDataImportValidation : s123456 [SQLSTATE 01000]

    All the @SQL statements in the SPs are set to NVARCHAR(4000).  The table column sizes are matching the original database table sizes.  I tried putting PRINT throughout all three SPs wherever there's a @SQL statement and run them in Query Analyzer and I get the same error as above.  Which SP is it failed on?  Is it failing on s123456 DB or is it failing on the next DB?  I have run this DB individually with the SPs and it would run successfully without an error.  I'm very new to SQL and not sure anymore what other tools I can use to troubleshoot this problem. 

    Any suggestion would be greatly appreciated!

    Thanks in advance.

    G

  • G

    You don't provide your table definitions, but I imagine that if you try to fit nvarchar(4000) data into several columns in the same table, it is unlikely to fit.  nvarchar(4000) has a data size of 8000 bytes, so it would be a badly-designed table that had more than one such column.

    The first thing I would try is to change the variables in your stored procedures (they start with @) to varchar.  This will halve their size at a stroke.

    If you're still having problems then please post your SPs and table definitions and somebody should be able to help.

    John

  • As a work around I run the store procedure as a Operating System Command for SQL Server Agent, calling the store procedure using OSQL

    osql -U dblogin -P dbpassword -S myservername\instance -d myDB -Q "exec sp_giving_prob"

  • Very simple.

    Recreate the job vis QA.

    This will solved the issue. Sometimes you simply cannot explain why MS products crash the way they do.

    Cheers

    Yohan

    yohanww@gmail.com

Viewing 4 posts - 1 through 3 (of 3 total)

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