Stored Procedure Runs complete in Query Analyzer, but not from a job step

Question

Stored Procedure Runs complete in Query Analyzer, but not from a job step

jaustin-722576

Grasshopper

Points: 22
More actions
April 23, 2008 at 4:29 pm

#185768

If someone can point me in the right direction, would be greatly appreciative.
Stored Procedure runs complete in Query Analyzer after about 50 minuts. But in a job step it runs successfully incomplete(does not populate table with data) within a second.
Job step command: Exec
Stored procedure:
CREATE procedure [dbo].[ss_ProjectTrackerDataInsert]
as
BEGIN
delete ss_TrackerData
insert into ss_TrackerData (
ProjectNumber ,
ForecastTerritory ,
RSM ,
AccountName ,
ProjectName ,
Channel ,
FulfillmentRepFirm ,
FulfillmentRepFirmSalesRep ,
DesignRepFirm ,
DesignRepFirmSalesRep ,
MarketSegment ,
RevenueType ,
FormFactor ,
Capacity ,
Interface ,
Temp ,
STEC ,
STMicro ,
Smart ,
SanDisk ,
MSystems ,
BitMicro ,
CompOther ,
TotalProjectRevenue ,
OurShareRevenue ,
ProjectStartDate ,
ProductionStartDate ,
SalesStep ,
ProductNumber ,
Price ,
Units01 ,
Units02 ,
Units03 ,
Units04 ,
Units05 ,
Units06 ,
Units07 ,
CommentsDate ,
TrackerComments
)
select mo.new_projectnumber,
mo.new_territoryname,
mo.owneridname,
mo.customeridname,
mo.name,
mo.new_trackerchannel,
mo.new_fulfillmentrepfirmname,
mo.new_fulfillmentrepfirmsalesrep,
mo.new_designrepfirmname,
mo.new_designrepfirmsalesrep,
mo.new_marketname,
mo.new_revenuetypename,
mop.new_formfactorname,
mop.new_capacityname,
mop.new_interfacename,
mop.new_operatingtempname,
CASE mo.new_comp_company_cb1name WHEN 'Yes' THEN 'X' ELSE NULL END,
CASE mo.new_comp_company_cb2name WHEN 'Yes' THEN 'X' ELSE NULL END,
CASE mo.new_comp_company_cb3name WHEN 'Yes' THEN 'X' ELSE NULL END,
CASE mo.new_comp_company_cb4name WHEN 'Yes' THEN 'X' ELSE NULL END,
CASE mo.new_comp_company_cb5name WHEN 'Yes' THEN 'X' ELSE NULL END,
CASE mo.new_comp_company_cb6name WHEN 'Yes' THEN 'X' ELSE NULL END,
mo.new_comp_company_other,
mo.new_tamrevenue,
mo.new_estrevenue,
mo.new_projectstartdate,
mo.new_productionstartdate,
mo.salesstagecodename,
mop.new_productnumber,
mop.new_price,
dbo.pt_func_forcast_period(GETDATE(), 0, mop.opportunityid, mop.productid),
dbo.pt_func_forcast_period(GETDATE(), 1, mop.opportunityid, mop.productid),
dbo.pt_func_forcast_period(GETDATE(), 2, mop.opportunityid, mop.productid),
dbo.pt_func_forcast_period(GETDATE(), 3, mop.opportunityid, mop.productid),
dbo.pt_func_forcast_period(GETDATE(), 4, mop.opportunityid, mop.productid),
dbo.pt_func_forcast_period(GETDATE(), 5, mop.opportunityid, mop.productid),
dbo.pt_func_forcast_period(GETDATE(), 6, mop.opportunityid, mop.productid),
mo.new_commentsdate,
mo.new_trackercomments
FROM ss_view_FilteredOpportunity mo LEFT OUTER JOIN
ss_view_FilteredOpportunityProduct mop ON mo.opportunityid = mop.opportunityid
WHERE mo.statecode = 0
END
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 1

Can you check the Job history details? It may be the user context that the job is running under that may be cuasing the issue.

jaustin-722576 Grasshopper Points: 22 More actions · Answer 2

jaustin-722576

Grasshopper

Points: 22

April 23, 2008 at 4:55 pm

#806339

Attached is the job history for this job_id.

Thanks

ksullivan SSCrazy Points: 2346 More actions · Answer 3

Is there any chance you have 2 databases, one with data and one without, and the job is using the wrong database, most likely master? The posted code does not specify a db--exec mydb.dbo.myproc or select ...from mydb.dbo.mytable. Stranger things have happened.