EXEC throw string or binary data would be truncated

Question

Below query throwing error in some cases

Declare @Col Varchar(100)
Select @ClientDocFormat=NoticeFormat From ClientDocumentFormat Where ClientID=@ClientID And FormatTypeID=@FormatType
Set @mCursor=Cursor For Select Fields,DATA_TYPE From V_EmployeeMasterFields
Open @mCursor
Fetch Next From @mCursor Into @ColumnName,@DATA_TYPE
WHILE @@FETCH_STATUS = 0
Begin
     Set @Col=REPLACE(@ColumnName,'@@','')
     If @DATA_TYPE='date'
        Set @Col='CONVERT(Varchar,'+@Col+',106)'
     Set @ClientDocFormat=REPLACE(@ClientDocFormat,@ColumnName,('!!+IsNull('+@Col+','''')+!!'))

Fetch Next From @mCursor Into @ColumnName,@DATA_TYPE
End
CLOSE @mCursor
DEALLOCATE @mCursor
Set @ClientDocFormat='Select '''+REPLACE( Replace(@ClientDocFormat,'''',''''''),'!!','''')+''' As DocFormat From V_EmployeeDetails Where EmpID='''+CONVERT(Varchar(20), @EmpID)+''''
EXEC(@ClientDocFormat)

Below is Error:

DocFormat
Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.

print @ClientDocFormat before EXEC . this will help you identify the issue — ughai
– ughai, Commented May 29, 2015 at 10:08
You missed to provide all variable declarations. The "CONVERT(Varchar,'+@Col+',106)" uses only the default number of characters. When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30. msdn.microsoft.com/en-us/library/ms176089.aspx — d_schnell
– d_schnell, Commented May 29, 2015 at 10:16

Kaushik Thanki · Accepted Answer · 2017-02-23 17:04:48Z

1

Here, The field is NOT big enough to hold my data.

Check the table structure of tables. I think you'll find that the length of one or more fields is NOT big enough to hold the data you are trying to insert / select / holding variable . For example, if the Phone field is a varchar(8) field, and you try to put 11 characters in to it, you will get this error.

edited Feb 23, 2017 at 17:04

answered May 29, 2015 at 13:28

Kaushik Thanki

3,5784 gold badges29 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EXEC throw string or binary data would be truncated

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related