JOB failed and QA

Hi,
I had created a sp run as a job, daily. if I execute this sp from QA, it's
work, clean.
but if I run the job, it failed with message (job details):
...ta would be truncated. [SQLSTATE 22001] (Error 8152) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) [SQLSTATE 01000]
(Error 0) The statement has been terminated. [SQLSTATE 01000] (Error 3621)
[SQLSTATE 01000] (Error 0) [SQLSTATE 01000] (Error 0) The statement has
been terminated. [SQLSTATE 01000] (Error 3621) [SQLSTATE 01000] (Error 0)
[SQLSTATE 01000] (Error 0) [SQLSTATE 0... The step failed.
the sp create a temporary table, fill some fields with sum() from other
table, then calculate the stock field from these fields value. at the end of
sp, the temporary table will update a table.
did I missed something?
I use SQL 7 sp 1 on NT4.
thanks in advance.
TeguhI think it's a data types problem too. so I've copied the exact columns from
the target table. all numeric type is decimal(18,2). There's no error
message when run from QA, it's fully worked.
both from QA and JOB I use the same command:
exec spproc_McUnscanQCMFG
but the job still fail. is there any different conditions for job and QA?
here the script:
Select [Item Code], [Item Name], [Stock PPS], [Stock Calc], [Differences],
[Beg. Stock], [Not Finish1Lot], Claim,
[MPC In], [MPC Out], [Scan MC], [WH Location], UOM, [Type]
Into #CheckInvMfg
From (Select IL_ItemCode as [Item Code], PartBom_Partname as [Item
Name],
IL_QuantityOnLocation as [Stock PPS],
IL_QuantityOnLocation as [Stock Calc],
IL_QuantityOnLocation as [Differences], IL_QuantityOnLocation
as [Beg. Stock],
IL_QuantityOnLocation as [Not Finish1Lot],
IL_QuantityOnLocation as Claim, IL_QuantityOnLocation as [MPC
In], IL_QuantityOnLocation as [MPC Out],
IL_QuantityOnLocation as [Scan MC], IL_WHLocation as [WH
Location],
PartBom_UnitOfMeasure as UOM, PartBOM_Type AS [Type]
From Tb_Ic_ItemLocation, Tb_Mst_PartBom Where Il_ItemCode =PartBom_partCode ) Mfg
-- Get / calculate Claim for Mfg
Update #CheckInvMfg
Set Claim = QtyClaim
From (Select cl_MatCode,
CASE CL_UOMClaim WHEN 'KG' THEN Sum( cl_ClaimQty) *
1000)
ELSE Sum(cl_ClaimQty)
END as QtyClaim
From tb_IC_Claim
Where Month(cl_lastupdate) = @.i_Periode and
Year(cl_lastupdate) = @.i_Tahun
and cl_moveto = @.i_MfgWarehouse and cl_postedstatus ='Posted'
Group by cl_matcode, CL_UOMClaim ) claim
Where cl_MatCode = [Item Code]
......
"jobi" <jobi@.reply2.group> wrote in message
news:bdph9e$jio$1@.reader08.wxs.nl...
> execute the same query in QA and put a "select min(sum_result ) ,
> max(sum_result ) from (yourquery) q " on it.
> Check for allowed values for them.
> Look at BOL for "Data Types"
> jobi|||The behaviour of the sp should be the same in QA than in sqlagent.
Are you sure you are running the same object ? (you don't specify e.g.
dbo.spproc_McUnscanQCMFG)
Check out if there exists more than one spproc_McUnscanQCMFG (with different
owners)
If not, check ms-site for issues with sql7 sp1 (btw sp4 is the current one)
jobi
"Teguh R" <priska@.hotpop.com> wrote in message
news:ORQmXgSQDHA.3144@.tk2msftngp13.phx.gbl...
> I think it's a data types problem too. so I've copied the exact columns
from
> the target table. all numeric type is decimal(18,2). There's no error
> message when run from QA, it's fully worked.
> both from QA and JOB I use the same command:
> exec spproc_McUnscanQCMFG
> but the job still fail. is there any different conditions for job and QA?
>
> here the script:
> Select [Item Code], [Item Name], [Stock PPS], [Stock Calc], [Differences],
> [Beg. Stock], [Not Finish1Lot], Claim,
> [MPC In], [MPC Out], [Scan MC], [WH Location], UOM, [Type]
> Into #CheckInvMfg
> From (Select IL_ItemCode as [Item Code], PartBom_Partname as [Item
> Name],
> IL_QuantityOnLocation as [Stock PPS],
> IL_QuantityOnLocation as [Stock Calc],
> IL_QuantityOnLocation as [Differences],
IL_QuantityOnLocation
> as [Beg. Stock],
> IL_QuantityOnLocation as [Not Finish1Lot],
> IL_QuantityOnLocation as Claim, IL_QuantityOnLocation as
[MPC
> In], IL_QuantityOnLocation as [MPC Out],
> IL_QuantityOnLocation as [Scan MC], IL_WHLocation as [WH
> Location],
> PartBom_UnitOfMeasure as UOM, PartBOM_Type AS [Type]
> From Tb_Ic_ItemLocation, Tb_Mst_PartBom Where Il_ItemCode => PartBom_partCode ) Mfg
> -- Get / calculate Claim for Mfg
> Update #CheckInvMfg
> Set Claim = QtyClaim
> From (Select cl_MatCode,
> CASE CL_UOMClaim WHEN 'KG' THEN Sum( cl_ClaimQty) *
> 1000)
> ELSE Sum(cl_ClaimQty)
> END as QtyClaim
> From tb_IC_Claim
> Where Month(cl_lastupdate) = @.i_Periode and
> Year(cl_lastupdate) = @.i_Tahun
> and cl_moveto = @.i_MfgWarehouse and cl_postedstatus => 'Posted'
> Group by cl_matcode, CL_UOMClaim ) claim
> Where cl_MatCode = [Item Code]
> ......
>
>
> "jobi" <jobi@.reply2.group> wrote in message
> news:bdph9e$jio$1@.reader08.wxs.nl...
> > execute the same query in QA and put a "select min(sum_result ) ,
> > max(sum_result ) from (yourquery) q " on it.
> >
> > Check for allowed values for them.
> > Look at BOL for "Data Types"
> > jobi
>
>|||IMO the "change the data type to exact type of target table" solved your
problem.
The upgrade to sp4 should make you sqlserver more reliable.
jobi
"Teguh R" <priska@.hotpop.com> wrote in message
news:uXfmJDATDHA.2148@.TK2MSFTNGP11.phx.gbl...
> I've installed sp4 and change the data type to exact type of target table,
> it's work now.
> But I still wonder, why previously it's works in QA but failed in JOB
> execution?
> I mean, QA has loose checking, less strong type setting parameters than
JOB
> execution?
>
> "jobi" <jobi@.reply2.group> wrote in message
> news:be39nd$gda$1@.reader08.wxs.nl...
> > The behaviour of the sp should be the same in QA than in sqlagent.
> >
> > Are you sure you are running the same object ? (you don't specify e.g.
> > dbo.spproc_McUnscanQCMFG)
> > Check out if there exists more than one spproc_McUnscanQCMFG (with
> different
> > owners)
> > If not, check ms-site for issues with sql7 sp1 (btw sp4 is the current
> one)
> >
> > jobi
> >
> >
> > "Teguh R" <priska@.hotpop.com> wrote in message
> > news:ORQmXgSQDHA.3144@.tk2msftngp13.phx.gbl...
> > > I think it's a data types problem too. so I've copied the exact
columns
> > from
> > > the target table. all numeric type is decimal(18,2). There's no error
> > > message when run from QA, it's fully worked.
> > >
> > > both from QA and JOB I use the same command:
> > >
> > > exec spproc_McUnscanQCMFG
> > >
> > > but the job still fail. is there any different conditions for job and
> QA?
> > >
> > >
> > >
> > > here the script:
> > >
> > > Select [Item Code], [Item Name], [Stock PPS], [Stock Calc],
> [Differences],
> > > [Beg. Stock], [Not Finish1Lot], Claim,
> > > [MPC In], [MPC Out], [Scan MC], [WH Location], UOM,
> [Type]
> > > Into #CheckInvMfg
> > > From (Select IL_ItemCode as [Item Code], PartBom_Partname as
> [Item
> > > Name],
> > > IL_QuantityOnLocation as [Stock PPS],
> > > IL_QuantityOnLocation as [Stock Calc],
> > > IL_QuantityOnLocation as [Differences],
> > IL_QuantityOnLocation
> > > as [Beg. Stock],
> > > IL_QuantityOnLocation as [Not Finish1Lot],
> > > IL_QuantityOnLocation as Claim, IL_QuantityOnLocation as
> > [MPC
> > > In], IL_QuantityOnLocation as [MPC Out],
> > > IL_QuantityOnLocation as [Scan MC], IL_WHLocation as [WH
> > > Location],
> > > PartBom_UnitOfMeasure as UOM, PartBOM_Type AS [Type]
> > > From Tb_Ic_ItemLocation, Tb_Mst_PartBom Where Il_ItemCode => > > PartBom_partCode ) Mfg
> > >
> > > -- Get / calculate Claim for Mfg
> > > Update #CheckInvMfg
> > > Set Claim = QtyClaim
> > > From (Select cl_MatCode,
> > > CASE CL_UOMClaim WHEN 'KG' THEN Sum( cl_ClaimQty)
*
> > > 1000)
> > > ELSE Sum(cl_ClaimQty)
> > > END as QtyClaim
> > > From tb_IC_Claim
> > > Where Month(cl_lastupdate) = @.i_Periode and
> > > Year(cl_lastupdate) = @.i_Tahun
> > > and cl_moveto = @.i_MfgWarehouse and
cl_postedstatus
> => > > 'Posted'
> > > Group by cl_matcode, CL_UOMClaim ) claim
> > > Where cl_MatCode = [Item Code]
> > > ......
> > >
> > >
> > >
> > >
> > > "jobi" <jobi@.reply2.group> wrote in message
> > > news:bdph9e$jio$1@.reader08.wxs.nl...
> > > > execute the same query in QA and put a "select min(sum_result ) ,
> > > > max(sum_result ) from (yourquery) q " on it.
> > > >
> > > > Check for allowed values for them.
> > > > Look at BOL for "Data Types"
> > > > jobi
> > >
> > >
> > >
> >
> >
>