String or Binary data would be truncated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

String or Binary data would be truncated

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-14 : 06:24:40

Hi All,

I have 1 SP which has one Parameter, say @Param_1 VarChar(7000)

when i execute this SP, i get error like:
Mgs 8152, Level 16, Stage 14, Line 1,
String or Binary data would be truncated.

Then i checked the Len of the parameter. Its less than 2500. Can anybody tell me, what this err is?

thanks in advance,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 06:29:59

The error most most likely thrown inside the SP.
Please post full code for SP.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-14 : 06:31:41

The error simply means you are trying to fit more data than what a column size is defined for.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-14 : 06:41:08

quote:
Originally posted by harsh_athalye

The error simply means you are trying to fit more data than what a column size is defined for.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thats what i hv posted in my 1st post. i hv param with varchar(7000) and whatever i m passing to the SP, i check the length of the param. its near to 2400-2500. not more than that. so i thing prob may be diff.

thanks,

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-14 : 06:51:02

We can not guess what is the table structure and how you are mapping your parameters to the table. Post the information asked if you want accurate help.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-14 : 07:14:04

quote:
Originally posted by harsh_athalye

We can not guess what is the table structure and how you are mapping your parameters to the table. Post the information asked if you want accurate help.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Hi Harsh,

Field width is 55 for which i have this param.

Create Table ABC(
No Numeric,
Field_1 varchar(55))

n my SP is total dynamic. do u need more info?

thanks,

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-14 : 07:16:20

So, are you trying to fit 2500 character data in the field whose size is 55?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-14 : 07:29:28

quote:
Originally posted by harsh_athalye

So, are you trying to fit 2500 character data in the field whose size is 55?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

No ... no

my Select statement is:

Select ....

Where Field_1 In ('<here comes the data with length near to 2400-2005>') ...

these r comma separated values. if i passes param (len -> 104-105) SP executes. but it crosses 130-135 it show the err.

plz suggest,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 07:46:03

We still have to guess!
Please post FULL CODE.

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-14 : 07:53:24

quote:
Originally posted by Peso

We still have to guess!
Please post FULL CODE.

Peter Larsson
Helsingborg, Sweden

ok ...

this is my SP:

CREATE Procedure [dbo].[nsp_TSR_TimetypefilterByTrackingFields]
(
@keyCompany integer,
@Role int,
@prSystem int,
@keyMAster int,
@startDate smallDateTime=null,
@endDate smallDateTime=null,
@TF1 varchar(4000)='',
@TF2 varchar(4000)='',
@TF3 varchar(4000)='',
@TSStatus varchar(100)='CO',
@useCalc int,
@DTRate float,
@OTRate float,
@VacRate float,
@sickRate float,
@RegRate float,
@DB int
)
As
SET NOCOUNT ON

declare @sSql varchar(7999)
declare @empFilter varchar(1000)
declare @columns varchar(1000)
declare @dispCols varchar(1000)
declare @Filter varchar(1000)
declare @payCol varchar(250)
declare @Calcstr varchar(100)
declare @dbName varchar(100)
declare @TFFilter1 varchar(4000)
declare @TFFilter2 varchar(4000)
declare @TFFilter3 varchar(4000)
declare @totCol varchar(100)

SET @empFilter=''
SET @columns=''
SET @dispCols=''
SET @filter=''
SET @payCol=''
SET @TFFilter1=''
SET @TFFilter2=''
SET @TFFilter3=''

IF @prsystem in (0, 1, 2, 3, 4, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 21, 28) --DIRECT HR COMPANIES
BEGIN
SET @EmpFilter = ' Inner Join iHRBAse As HRB With (NoLock) On iMAster.iKey = HRB.iKeyMAster
Inner Join iHRWork As HRC With (NoLock) On HRB.iKey = HRC.iKeybAse '

SET @columns=' HRC.iEmployeeNo, '

SET @dispCols=' IsNull(HRC.iEmployeeNo,'''') As EmpNo '
END
ELSE
BEGIN
SET @EmpFilter = ' Inner Join oHRBAse As HRB With (NoLock) On iMAster.iKey = HRB.oKeyMAster
Inner Join oHRCurrent As HRC With (NoLock) On HRB.oKey = HRC.oKeyBAse '

SET @columns = ' HRC.ojDepartment, '

IF @prsystem in (5,19,41) --SOURCE/CERIDIAN COMPANIES
BEGIN
SET @columns = ' HRC.oClock ' --THIS IS DONE SO THAT IT EAsY TO RETRIVE DATA IN FRONTEND
SET @dispCols = @dispCols +' IsNull(HRC.oClock,'''') As EmpNo, '
END
ELSE
BEGIN
SET @columns = ' HRC.oEmpNo '
SET @dispCols = @dispCols + ' IsNull(HRC.oEmpNo,'''') As EmpNo,'
END
END

IF @TF1<>'' And @TF1<>'ALL'
BEGIN
CREATE TABLE #Tempfilter1
(
tempvalue varchar(100)
)
exec nsp_RPT_insertInTempTable '#Tempfilter1',@TF1
SET @TFFilter1 = ' Left Outer Join #Tempfilter1 f1 ON tl.iTF1 = f1.tempvalue '
CREATE CLUSTERED INDEX [IX_temp1] ON [#Tempfilter1]([tempvalue])
END

IF @TF2<>'' And @TF2 <>'ALL'
BEGIN
CREATE TABLE #Tempfilter2
(
tempvalue varchar(100)
)
exec nsp_RPT_insertInTempTable '#Tempfilter2',@TF2
SET @TFFilter2 = ' Left Outer Join #Tempfilter2 f2 ON tl.iTF2 = f2.tempvalue '
CREATE CLUSTERED INDEX [IX_temp2] ON [#Tempfilter2]([tempvalue])
END

IF @TF3<>'' And @TF3<> 'ALL'
BEGIN
CREATE TABLE #Tempfilter3
(
tempvalue varchar(100)
)
exec nsp_RPT_insertInTempTable '#Tempfilter3',@TF3
SET @TFFilter3 = ' Left Outer Join #Tempfilter3 f3 ON tl.iTF3 = f3.tempvalue '
CREATE CLUSTERED INDEX [IX_temp3] ON [#Tempfilter3]([tempvalue])
END

IF @UseCalc = 0 SET @Calcstr = ' And tl.iCalculated In (2,3) ' -- Calculated Timesheets (default)
IF @UseCalc = 1 SET @Calcstr = ' And tl.iCalculated In (1,3) ' -- Original Timesheets

IF @Role = 16 SET @Filter = ''
IF @Role = 8 -- Manager Role - apply manager filter
BEGIN
Select @Filter = iMgrFilter From iMAster WITH(nolock) Where iKey = @KeyMAster
IF Len(@Filter)>0
BEGIN
SET @Filter=Replace(@Filter, '~', char(39))
SET @Filter=Replace(@Filter, 'oHRCurrent.','HRC.')
SET @Filter = ' And (' + @Filter + ')'
END
ELSE
SET @Filter = ' And iMAster.iKey = -64 ' -- This is a dummy filter IF no filter is found
END

IF @Role = 4 SET @Filter =' Inner Join oHROrganization With (NoLock) ON iMaster.iKey = oHROrganization.iKeyEmp And oHROrganization.iKeySup = ' + Convert(varchar(10), @KeyMAster)

IF @DB >= 2004
SET @DBname = ''
ELSE
SET @DBname = ' iProductArchive' + convert(varchar(4),@DB) + '.dbo.'

SET @payCol=' Convert(Decimal(10,2), IsNull((Select Top 1 omHourlyRate From oHRCompHistory With (NoLock) Where okeyBAse = HRB.okey And omDateBeg <='''+ convert(varchar,@startDate)+''' Order by omDateBeg desc),0))'

SET @totCol=' Convert(Decimal(10,2),Sum(IsNull(ti.iHours,0)))'

SET @sSQL = '
Select ' + @dispcols + @payCol + ' As PayType, iLAstName, iFirstName, IsNull(TFI.cName,'''')As cName, iTF1, tl.iTimeType, ' + @totCol + ' As Hours, IsNull(LI1.sDescrip,'''')As Department, IsNull(LI2.sDescrip,'''')As Division,
RateMultiplier=(
cAse tl.iTimeType
when ''Regular'' then '+convert(varchar,@regRate)+'
when ''Over Time'' then '+convert(varchar,@OTRate) + '
when ''Double Time'' then '+convert(varchar,@DTRate) + '
ELSE ''1''
END
),
TotalHours = Convert(Decimal(10, 2),(
cAse tl.iTimeType
when ''Regular'' then '+@payCol+'*'+@totCol+'*'+convert(varchar,@regRate)+'
when ''Over Time'' then '+@payCol+'*'+@totCol+'*'+convert(varchar,@OTRate)+'
when ''Double Time'' then '+@payCol+'*'+@totCol+'*'+convert(varchar,@DTRate)+'
ELSE '+@payCol+'*Sum(IsNull(ti.iHours,0))*1
END
)) From iMaster With (NoLock)
' + @EmpFilter + '
' + @Filter + '
Inner Join ' + @DBname + 'iTimesheetBase As tb With (NoLock) ON iMaster.iKey = tb.iKeyMAster
Inner Join ' + @DBname + 'iTimesheetLine As tl With (NoLock) ON tb.iKey = tl.iKeyBAse ' + @Calcstr + '
Inner Join ' + @DBname + 'iTimesheetItem As ti With (NoLock) ON tl.iKey = ti.iKeyLine And ti.iDate BETWEEN '''+ convert(varchar,@StartDate)+ ''' And '''+ convert(varchar,@enddate) +'''
Inner Join '++'.dbo.sEarnings As E With (NoLock) on imAster.ikeycompany = e.skeycompany
And e.sTimeType=1 And sKEyCompany='+ Convert(Varchar(10),@Keycompany) + ' And tl.iTimeType=E.sDescription

LEFT JOIN sListBAse As lb1 With (NoLock) on iMAster.iKeyCompany = LB1.sKeyCompany And lb1.sKeyCompany = '+ Convert(Varchar(10),@Keycompany) + ' And lb1.sDescrip=''Departments''
LEFT OUTER JOIN sListItems As LI1 With (NoLock) On lb1.sKey = LI1.sKeyBAse And LI1.sValue=HRC.ojDepartment ' -- And LI1.sValue=HRC.ojDepartment

SET @sSQL = @sSQL + ' LEFT JOIN sListBAse As lb2 With (NoLock) on iMAster.iKeyCompany = LB2.sKeyCompany And lb2.sKeyCompany = '+ Convert(Varchar(10),@Keycompany) + ' And lb2.sDescrip=''Divisions''
LEFT OUTER JOIN sListItems As LI2 With (NoLock) On lb2.sKey = LI2.sKeyBAse And LI2.sValue=HRC.ojDivision '

SET @sSQL = @sSQL + ' Inner Join cTFBAse As TFB With (NoLock) on iMAster.iKeyCompany = TFB.cKeyCompany And TFB.cKeyCompany ='+ convert(varchar,@Keycompany) +'
Inner Join cTFItem As TFI With (NoLock) on TFB.cKey = TFI.cKEYBAse And TFI.cName=tl.ITF1

'+ @TFFilter1 +'
'+ @TFFilter2 +'
'+ @TFFilter3 +'
Where iMAster.iKeycompany ='+ convert(varchar,@Keycompany)

IF @TSStatus<>'' SET @sSql=@sSql + ' And tb.iStatus IN('+@TSStatus+')'

-- Mahesh on 14/05/2007
-- VTL: Timesheet Reports - Other - Fischione - Custom TAsk Hours by Project Code\Source
IF @TF1 <> '' SET @sSql=@sSql + ' And tl.iTF1 IN(' + @TF1 + ')'

set @sSql=@sSql + ' Group By iTF1,LI1.sDescrip,'+@columns+',iMAster.iLAstName,iMAster.iFirstName,E.sOrder,tl.itimeType,HRB.oKey,LI2.sDescrip,TFI.cName
Order By ' + @columns + ', iMAster.iLAstName, iMAster.iFirstName, tl.iTimeType OPTION (FORCE ORDER) ' -- By Danilo on 11/08/2005

-- Old --> Order By iTF1,LI1.sDescrip,'+@columns+',iMAster.iLAstName,iMAster.iFirstName,E.sOrder,TFI.cName,tl.iTimeType OPTION (FORCE ORDER) ' -- By Danilo on 11/08/2005
-- New --> Order By HRC.oEmpNo ,iMAster.iLAstName, iMAster.iFirstName,tl.iTimeType -- by Mahesh on 10/05/2007

Print @sSql
EXECUTE (@sSql)

if @TF1 <> '' And @TF1 <> 'ALL' drop table #tempFilter1
if @TF2 <> '' And @TF2 <> 'ALL' drop table #tempFilter2
if @TF3 <> '' And @TF3 <> 'ALL' drop table #tempFilter3

n params to this SP is

exec nsp_TSR_TimetypefilterByTrackingFields
@keyCompany=N'6902',@Role=N'16',@PRSystem=N'33',@keyMaster=N'0',@startDate=N'5/1/2007'
,@EndDate=N'5/31/2007',
@TF1=N'''200'', ''500 - General
R&D'', ''501 - Support: Electropolisher & Controls'', ''506 - Support: Dimple Grinder'', ''507 - Support: Punch and Hand Grinder'', ''508 -
Support: Disk Cutter'', ''509 - Support: Ion Mill'', ''511 - Support: Plasma Cleaner'', ''513 - Support: TEM Specimen Holders'', ''514 -
Support: Vacuum Storage'', ''521 - Support: ADF'', ''801 - General & Administrative'', ''804 - General Overhead'', ''808 - Sales &
Marketing'', ''1040 - Devel: ADF/JEOL 795 mount'', ''1041 - Devel: New Control Platform'', ''1042 - Devel: High-Mag Ion Mill'', ''1043 -
Devel: New Ion Gun'', ''1045 - Devel: Holder, High-Tilt Tomography'', ''1046 - Devel: Detector, BF/DF, FEI'', ''1047 - Devel: ADF/JEOL 35mm
mount'', ''1048 - Devel: ADF/Hitachi HF-2200/2000'', ''1049 - Devel: Life-Sciences/TEM prep'', ''1050 - Devel: Holder, Electrical Contact'',
''1051 - Devel: ADF/Leo Libra'', ''1052 - Devel: SEM Dewar'', ''1053 - Devel: Holder, Magnetic'', ''1054 - Devel: Dimpling Grinder Gen2'',
''1055 - Devel: ADF JEOL 2100'', ''1056 - Devel: ADF LEO
Pre-Filter''',@TF2=N'ALL',@TF3=N'ALL',@TSStatus=N'''CO''',@UseCalc=0,@DTRate=N'2',@OTRate=N'1.5',@VacRate=1,@SickRate=1,@RegRate=N'1',@DB=N'2004'

hopes i hv supplied all the info.

Thanks,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 08:18:45

And on which line does the error appear?

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-14 : 10:01:58

quote:
Originally posted by Peso

And on which line does the error appear?

Peter Larsson
Helsingborg, Sweden

thanks PESO for ur efforts. Actually in this case i did not changed the size of Field for the Temp tables in which i insert these params. I have resolved it.

... Before
CREATE TABLE #Tempfilter1
(
tempvalue varchar(100)
)

... Now
CREATE TABLE #Tempfilter1
(
tempvalue varchar(4000)
)

Thanks All,

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources