Table of Contents
SQL Server Error : 8152 Details
SQL Server Error: 8152
Severity: 16
Event Logged or not: No
Description:
String or binary data would be truncated.
Severity 16 Description:
Indicates general errors that can be corrected by the user.
sql server error code 8152 or sql server error msg 8152 level 16 state 14
Explanation
At runtime, while inserting the data from DB2 table into SQL Server table, receiving the error “ERRORCODE: 8152 Error Message: [SQLServer JDBC Driver][SQLServer]String or binary data would be truncated”.
This error is usually encountered when inserting a record in a table where one of the columns is a VARCHAR or CHAR data type and the length of the value being inserted is longer than the length of the column.
Cause of error
The value retrieved from the source table gets converted into an encoded format which leads to an increase in data size and while inserting this data in SQL server table the above error occurs.
Alternate error messages covered:
- sql server error 8152 state 30 or sql server sql error 8152 sqlstate 22001
- sql server error is 8152 state 14 or microsoft sql server error 8152
- sql server error is 8152 state 2 or ms sql server error 8152
- microsoft_sql_server error number 8152 or sql server error 8152 state 10
- sql server error is 8152 state 10 or sql server error 8152 string
Solution
Use the varchar function in the select statement of the ‘Execute Query’ action to decode the data before entering it into the SQL Server table.
Consider the following query, which shows how to use the varchar function for db2 database to sql server:
Select VARCHAR(fieldname, fieldmaxlength, 37) FROM TableName.
Error related is “The character code set identification CCSID 37 is utilised for fields in the DB2 source database, causing this error.”
To prevent this error from happening when inserting data into a table or when updating existing data in a table we need to always make sure that the string value you are trying to insert into your table can fit to the column we have specified.
If needed to insert the data to the table then one option would be to increase the length of the VARCHAR/CHAR column using the ALTER TABLE command:
Alter Table [dbo].[Table1] ALTER COLUMN [ColZ] VARCHAR(500)If the error is caused by the decreasing of the length of the column, first check the maximum length that the column have and use that as the new length of the column. To determine the maximum length of a particular column, you can do the following:
Select Max(LEN([ColZ])) FROM [dbo].[Table1]This will return the length of the longest [ColZ]. Then use this value as the new length of your column if you need to decrease its length.
SQL Server Error Code and solution summary
SQL Server Error: 8152
Severity: 16
Event Logged or not: No
Description:
String or binary data would be truncated.
Increase of the column length to which data is trying to be inserted is solution for this truncation error.