mystery behaviour in stored procedure /String or binary data would be truncated.The statement has been terminated.
-
Any ideas ?
This syntax worked fine until yesterday
*******************************************
create proc myProc
as
insert into imp_table
select
s.number as Number,
s.text as [S_Text],
s.vehiclepartnumber as [S_VehiclePartNumber],
s.[OwnerId]as [OwnerId],
s.OfferNumber as offernumber,
s.Category as Category,
s.Status as status,
s.Price as price,
s.VehicleCategory as VehicleCategory,
s.partYear as PartYear,
l.Number as V_Number,
l.[Text] as text,
l.VehiclePartNumber as VehiclePartNumber,
l.OwnerId as V_OwnerId,
l.makeid as makeid,
l.status as V_Status,
l.category as V_Category,
l.VehiclePartGroupNumber as VehiclePartGroupNumber,
l.Modified as Modified,
l.SortOrder as SortOrder,
l.BodyType as BodyType,
l.Depreciation as Depreciation,
l.NewPrice as NewPrice,
l.[Timestamp] as [Timestamp],
l.partyear as V_PartYear,
l.ShortName as shortname,
sv.id as regnumber,
sv.companyid as companyid,
o.featureremark
.... follows with where clause
*******************************************
now I received following error :
String or binary data would be truncated.The statement has been terminated.
Yes , I checked it out , no overflow with character fields , no nvarchars etc. The target
table matched with datatypes, lenghts and column counts with select
where clause has no effect ... so I omitted from examples (believe me , tested that one too ...)
**********************************************************************
Solution was to provide target table column list. Following syntax worked :
*********************************************************************************
create proc myProc
as
insert into imp_table
(
Number,
[S_Text],
[S_VehiclePartNumber],
[OwnerId],
offernumber,
Category,
status,
price,
VehicleCategory,
PartYear,
V_Number,
text,
VehiclePartNumber,
V_OwnerId,
makeid,
V_Status,
V_Category,
VehiclePartGroupNumber,
Modified,
SortOrder,
BodyType,
Depreciation,
NewPrice,
[Timestamp],
V_PartYear,
shortname,
regnumber,
companyid,
featureremark)
select
s.number as Number,
s.text as [S_Text],
s.vehiclepartnumber as [S_VehiclePartNumber],
s.[OwnerId]as [OwnerId],
s.OfferNumber as offernumber,
s.Category as Category,
s.Status as status,
s.Price as price,
s.VehicleCategory as VehicleCategory,
s.partYear as PartYear,
l.Number as V_Number,
l.[Text] as text,
l.VehiclePartNumber as VehiclePartNumber,
l.OwnerId as V_OwnerId,
l.makeid as makeid,
l.status as V_Status,
l.category as V_Category,
l.VehiclePartGroupNumber as VehiclePartGroupNumber,
l.Modified as Modified,
l.SortOrder as SortOrder,
l.BodyType as BodyType,
l.Depreciation as Depreciation,
l.NewPrice as NewPrice,
l.[Timestamp] as [Timestamp],
l.partyear as V_PartYear,
l.ShortName as shortname,
sv.id as regnumber,
sv.companyid as companyid,
o.featureremark
.... followed with where clause
**************************************************************************
bit more sugar and chocolate ... both insert into ... select -sentences work well in Query Analyzer
( sp executed under SA-account , likewise insert into ... select -sentences in QA executed under SA account
-
Have you tried running in debug to try and isolate exactly when the error appears?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
It's a best practice to always explicitly give the column names in the select and insert statement.
Though you checked everything, either the column order of the table is chanegd or length of string column in the select statement exceeds the definition. Since the mystoeriou is gone after you add the column name in the insert statement, most likely is the column order of the table is changed.
Run
select name, colorder from syscolumns where id=object_id('YourTableName')
check the column order is the same or not with the select statement.
-
thanks peterhe!
Yes, I agree "It's a best practice to always explicitly give the column names in the select and insert statement."
SP and table were sort of quick test for report repository so I was bit lazy with this one.
Anyway ... your answer seems to be logical one so thank you very much.
Being bit paranoid (which doesn't mean they are not after me ) it's always a comfort to know that I'm not dealing with "hidden feautures"
esa hok
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply