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.

SC

Based on the fact that I can't see your DTS workflow diagram, I'd urge you to
check the workflow.


ML

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

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

who's the job running under?  You tell it to create a table that is
owned by dbo, but you don't insert into dbo.tablename.  Just wondering
if the system might be confused by an ownership issue.

grasping at straws here,
Stu

JDBC client - CURSOR as OUT parameter -supported ?
Just Venting
Recyle an identity column number when max is reached
Nested sets in SQL - inventor?
Performance question: Separate database or additional table?