none
C# DataAdapter Fill intermittent Error 8152 Stored Proc "String or binary data would be truncated. The statement has been terminated" RRS feed

  • Question

  • Here's a crazy issue we've been unable to trackdown that impact production, so could use some prompt assistance please:

    Error occuring with simple query to stored procedure with:

    DataSet ds = new DataSet();
    SqlDataAdapter da = new SqlDataAdapter(this.DbCommand);
    da.Fill(ds);
    return ds;

    The stored procedure has been in use for numerous years. Now in new environment Windows 2008 R2, SQL Server 2008R2, database running at Compatiblity Level 90, Visual Studio 2010, Framework 3.5

    Intermittently da.Fill() results in Error 8152 Stored Proc "String or binary data would be truncated. The statement has been terminated", BUT excuting the same command (captured in Profiler) in Management Studio is ALWAYS successful.

    Here's the punch line: Simply updating the Stored Procedure without any modification (open procedure, press F5) resolves the problem!

    1. Executing da.Fill works normally most of the time. Sometimes it results in 8152 error.

    2. Putting a break point on error, then executing Stored Procedure in SSMS result in no error.

    3. Update Stored Procedures by opening & executing without any change.

    4. Retry da.Fill, successful.

    Clues:

    • The error message "String or binary data would be truncated. The statement has been terminated" also includes some Print statement output.
    • Behavior seems unique to this Stored Procedure (length ~ 860 lines)
    • Seems to occur after a database restore, maybe
    • Occured this morning, after functioning normally up until leaving for the night. In the morning, running the application in VS 2010 that was still open (but not running) resulted in the error with each execution. Put breakpoint, verify Stored Proc runs OK in SSMS, trace in profiler. Reproduce error, verify OK in proc, etc. Open Stored Proc, F5, retry fill, application runs again with no error.

    Appreciate thoughts, solution, assistance on this crazy issue.

                    }

     

     
    Friday, January 25, 2013 4:13 PM

All replies

  • Wow, that is very bizarre! Here's a thought ... if you put a try/catch around that .Fill() statement and tried to run the .Fill() statement again if you get the exception, will it work then? Or is the fix always having to re-save the Stored Proc?

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, January 26, 2013 5:39 PM
  • Appreciate the info.

    Appears to be another compile is required.

    For now hacked by adding in the Catch for this particular procedure, execute sp_recompile & then redo the .Fill(). Works every time.

    Latest info: appears to be associated with backup & restore. After a restore or backup this behavior. Once compiled, no problem!

    Still seeking explanation and permanent fix...

    Wednesday, January 30, 2013 9:40 PM
  • Yeah, no idea on why that is happening ... but executing the sp_recompile in the catch is an excellent workaround, since it's something that doesn't happen very often (don't know why I didn't think of that! LOL!)

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, January 30, 2013 11:14 PM
  • Yea this can always happen if somebody has put something in the database which is corrupted in a way it is not accepted by the computer.

    Check your database using SQL QueryAnalyzer or use the SQL Profiler. 


    Success
    Cor

    Thursday, January 31, 2013 5:14 PM