I have a scheduled job where it's failing intermittently every few days.
The job fails in one of two steps, but with the same error above.
Step 18 is where I do a drop of the table and a create of the table, and
its indexes. It doesn't fail here, but fails in one of the next two
steps.
Step 19 is an insert into the table created in step 18. It takes a
source table, and puts into it mostly the same information, except that
it sums stuff up by purcase order instead of by line item. It also does
some max(dates) to record the most recent activity on the items in this
summary table.
Step 20 is an insert into the same table from the same source table as
in step 19, except that it processes everything excluded from the first
pass.
It's failed on step 19 & step 20 over the last several days. The error
message itself displays like:
"Executed as user: Mydomain\Mysqlaccount. Invalid Object Name
'tablename'. [SQLSTATE 42S02] (Error 208). The step failed."
To fix the problem, we've simply re-started in the failed step (did not
re-create the table, only picked up with the insert into it), and the
job has run to completion.
One thing that has occurred to me is to have the table drop, create and
load all in stored procedures, instead of how it is now - in TSQL code
within the batch job. I don't know if this would fix the problem though.
Any ideas as to what's happening? The info I've found on the 42S02
error hasn't been much help yet.
SCBased on the fact that I can't see your DTS workflow diagram, I'd urge you t
o
check the workflow.
ML|||> Based on the fact that I can't see your DTS workflow diagram, I'd urge you toed">
> check the workflow.
There is no DTS Workflow diagram.
These are just steps in a job created under the Management folder in
Enterprise Manager, under the SQL Server Agent, under the Jobs link.
It's 3 steps inside 1 of those jobs that is dying on step 19 or 20 with
the 42S02 Error 208 message.
18 runs okay, 19 & 20 are the ones that have died intermittently.
The steps are:
-- Step 18
CREATE TABLE [dbo].[posummary] (
[sSalesOrganization] [varchar] (8) NULL ,
[sSalesGroup] [varchar] (3) NULL ,
[sSalesRepCode] [varchar] (10) NULL ,
[sDistributionChannel] [varchar] (2) NULL ,
[sSoldTo] [varchar] (10) NULL ,
[sShipTo] [varchar] (10) NULL ,
[sCustName1] [varchar] (45) NULL ,
[sCustName2] [varchar] (35) NULL ,
[PO_Num] [varchar] (20) NULL ,
[Order_date] [smalldatetime] NULL ,
[Req_Ship_Date] [smalldatetime] NULL ,
[Inv_Date] [smalldatetime] NULL ,
[Inv_Nr] [varchar] (10) NULL ,
[lOrderQuantity] [int] NULL ,
[lOrderValue] [money] NULL ,
[lShipValue] [money] NULL ,
[OpenVal] [money] NULL ,
[lShipQuantity] [int] NULL ,
[sAccountSoldTo] [varchar] (10) NULL ,
[sMasterSoldTo] [varchar] (10) NULL
) ON [PRIMARY]
GO
CREATE INDEX [posummary] ON [dbo].[posummary]([sSalesOrganization],
[sDistributionChannel], [sSalesGroup], [sSalesRepCode], [sSoldTo],
[sShipTo], [PO_Num], [Inv_Nr]) ON [PRIMARY]
GO
CREATE INDEX [sorg] ON [dbo].[posummary]([sSalesOrganization]) ON
[PRIMARY]
GO
CREATE INDEX [distchnl] ON [dbo].[posummary]([sDistributionChannel])
ON [PRIMARY]
GO
CREATE INDEX [sgroup] ON [dbo].[posummary]([sSalesGroup]) ON [PRIMARY]
GO
CREATE INDEX [sRep] ON [dbo].[posummary]([sSalesRepCode]) ON [PRIMARY]
GO
CREATE INDEX [SoldTo] ON [dbo].[posummary]([sSoldTo]) ON [PRIMARY]
GO
CREATE INDEX [ShipTo] ON [dbo].[posummary]([sShipTo]) ON [PRIMARY]
GO
CREATE INDEX [PO] ON [dbo].[posummary]([PO_Num]) ON [PRIMARY]
GO
CREATE INDEX [InvNr] ON [dbo].[posummary]([Inv_Nr]) ON [PRIMARY]
GO
CREATE INDEX [Ord Date] ON [dbo].[posummary]([Order_date]) ON [PRIMARY]
GO
CREATE INDEX [ACCTSOLDTO] ON [dbo].[posummary]([sAccountSoldTo]) ON
[PRIMARY]
GO
CREATE INDEX [MASTERSOLDTO] ON [dbo].[posummary]([sMasterSoldTo]) ON
[PRIMARY]
GO
-- Step 19
insert into posummary
select
podetail.ssalesorganization,
podetail.Ssalesgroup,
podetail.ssalesrepcode,
podetail.sDistributionChannel,
podetail.ssoldto,
podetail.sshipto,
max(tblmaster_shipto.scustname1) as Custname1,
max(tblmaster_shipto.scustname2) as Custname2,
PO_Num,
max(Order_Date) as Order_Date,
max(Req_Ship_Date) as Req_Ship_Date,
max(Inv_Date) as Inv_Date,
max(Inv_Nr) as Inv_Nr,
sum(lorderquantity) as lorderquantity,
sum(lordervalue) as lordervalue,
sum(lshipvalue) as lshipvalue,
sum(lordervalue) - sum(lshipvalue) as Openval,
sum(lshipquantity) as lshipquantity,
max(tblmaster_accountsoldto.saccountsoldto) as AccountSoldTo,
max(tblmaster_accountsoldto.smastersoldto) as MasterSoldTo
from podetail
left outer join tblmaster_shipto on
((tblmaster_shipto.ssoldto = podetail.ssoldto) and
(tblmaster_shipto.sshipto = podetail.sshipto) and
(tblmaster_shipto.ssalesgrp = podetail.ssalesgroup) and
(tblmaster_shipto.ssalesrep = podetail.ssalesrepcode) and
(tblmaster_shipto.sdistrchnl = podetail.sdistributionchannel))
left outer join tblmaster_accountsoldto on
(tblmaster_accountsoldto.ssoldto = podetail.ssoldto)
and (tblmaster_accountsoldto.ssalesgroup not in ('R1','R2','R3'))
where podetail.sdistributionchannel != 'WL'
group by
podetail.ssalesorganization,
podetail.Ssalesgroup,
podetail.ssalesrepcode,
podetail.sDistributionChannel,
podetail.ssoldto,
podetail.sshipto,
PO_Num
-- step 20
insert into posummary
select
podetail.ssalesorganization,
podetail.Ssalesgroup,
podetail.ssalesrepcode,
podetail.sDistributionChannel,
podetail.ssoldto,
podetail.sshipto,
max(tblmaster_shipto.scustname1) as Custname1,
max(tblmaster_shipto.scustname2) as Custname2,
PO_Num,
max(Order_Date) as Order_Date,
max(Req_Ship_Date) as Req_Ship_Date,
max(Inv_Date) as Inv_Date,
max(Inv_Nr) as Inv_Nr,
sum(lorderquantity) as lorderquantity,
sum(lordervalue) as lordervalue,
sum(lshipvalue) as lshipvalue,
sum(lordervalue) - sum(lshipvalue) as Openval,
sum(lshipquantity) as lshipquantity,
max(tblmaster_accountsoldto.saccountsoldto) as AccountSoldTo,
max(tblmaster_accountsoldto.smastersoldto) as MasterSoldTo
from podetail
left outer join tblmaster_shipto on
((tblmaster_shipto.ssoldto = podetail.ssoldto) and
(tblmaster_shipto.sshipto = podetail.sshipto) and
(tblmaster_shipto.ssalesgrp = podetail.ssalesgroup) and
(tblmaster_shipto.ssalesrep = podetail.ssalesrepcode) and
(tblmaster_shipto.sdistrchnl = podetail.sdistributionchannel))
left outer join tblmaster_accountsoldto on
(tblmaster_accountsoldto.ssoldto = podetail.ssoldto)
and (tblmaster_accountsoldto.ssalesgroup in ('R1','R2','R3'))
where podetail.sdistributionchannel = 'WL'
group by
podetail.ssalesorganization,
podetail.Ssalesgroup,
podetail.ssalesrepcode,
podetail.sDistributionChannel,
podetail.ssoldto,
podetail.sshipto,
PO_Num|||Your insert statement is not optimal - it's missing the column list for the
destination table. Maybe that's it.
ML|||> Your insert statement is not optimal - it's missing the column list for the
> destination table. Maybe that's it.
You mean where I have:
insert into posummary
select
podetail.ssalesorganization,
podetail.Ssalesgroup,
podetail.ssalesrepcode,
podetail.sDistributionChannel,
podetail.ssoldto,
podetail.sshipto......
it should now read:
insert into posummary
select
sSalesOrganization = podetail.ssalesorganization,
sSalesGroup = podetail.Ssalesgroup,
sSalesRepCode = podetail.ssalesrepcode,
sDistributionChannel = podetail.sDistributionChannel,
sSoldTo = podetail.ssoldto,
sShipTo = podetail.sshipto,
Is that correct?
I didn't think that made a lot of difference. The error message surely
doesn't lead you in that direction. The error message makes you think
it cannot find the table.
BC
donMahya wrote: