Resources » Errors and Solutions » Windows Errors

SQL Error: String or binary data would be truncated. The statement has been terminated.


Last Updated:   Category: Windows Errors    
Author: Member Level: Gold    Points: 4



Problem Description:

The SQL Server error "String or binary data would be truncated. The statement has been terminated" happens when try to update/insert the sql table column with data having length more than the column length.


Consider the following example:

The table dgDownloadStatus consists of the following fields:

Column Name Data Type Length Allow Nulls

DownloadStatus nvarchar 50 1
FinishDownloadStatus nvarchar 50 1
ErrorDescription nvarchar 50 1

Here we have allocated 50 characters length for ErrorDescription column.


Assume the following error message: (Length greater than 50)

strErrorMessage = "Source SQL Connection Opening Failed: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"


But When we try to update the table with the following command




Try

strSql = "UPDATE dpGroupTable SET DownloadStatus = 'Failed', ErrorDescription='" & _
strErrorMessage & "' WHERE [Group] = '" & sGroupName & _
"' and [Table]='" & sTableName & "'"


ErrSqlCmd = New SqlCommand(strSql, connection)
ErrSqlCmd.ExecuteNonQuery()


You will get an error String or binary data would be truncated. The statement has been terminated.


Catch ex as Exception

Msgbox ex.Message '---- Here the exception error will be thrown

End Try



Solution Description:

1. Make sure the length of the column is long enough to hold the large error message.

2. Increase the length of the column

3. Or update the error message with the number of characters less than or equal to the length of the columns


Did you like this resource? Share it with your friends and show your love!

Responses to "SQL Error: String or binary data would be truncated. The statement has been terminated."
Author: Mayura Sangeene    04 Nov 2009Member Level: Bronze   Points : 1
Your Ans is really useful to me, the problem was of Column width only.

Thanks and Regards
Mayura Sangeene



Author: Viji RAJKUMAR    04 Nov 2009Member Level: Gold   Points : 1
Mayura,

glad to hear that my solution helped you.

Yes the problem is with column width , but we do get the error message in a different manner so that we might get confused..

Regards,

Viji Rajkumar



Guest Author: Ameenulla     11 Apr 2012
I am having same problem while inserting data(having few rows some of them are causing this issue) from one table to another table ,here my requirement is in case of valid rows it should insert and discard only invalid rows


Guest Author: ahsan     10 Oct 2012
Easily got solution, great!


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India