SQL recurring job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL recurring job

Author

Topic

rusmanicai
Starting Member

13 Posts

Posted - 2012-02-01 : 10:07:28

Hello,
I have created a SP and want to run it every day. It updates a field with a formula. When I run the update straight from the SP it does the job, the second day, when I check what the scheduled job did the data that was good yesterday is now wrong.
Any suggestions?

here is the SQL syntax:
"
update MaterialOnHand
set Materialonhand.UnitCost=(materialunitcost.UnitCost/(Material.Width/MaterialOnHand.Width))
from
MaterialOnHand
inner join Material on Material.MaterialCode=MaterialOnHand.MaterialCode
inner join MaterialUnitCost on MaterialOnHand.MaterialCode=MaterialUnitCost.MaterialCode
where MaterialOnHand.MaterialCode like '%-r'
and Material.MaterialCode=MaterialOnHand.MaterialCode
and Material.MaterialCode=MaterialUnitCost.MaterialCode
and (MaterialOnHand.CreateDatim<=GETDATE() or MaterialOnHand.Updatedatim<=GETDATE())"

and here is the syntax inside the job step.

Thank you!

RJulia

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-02-01 : 11:30:41

Without understanding your tables / data structures etc or seeing your results, I would suggest that maybe your proc is updating all the data including the stuff you updated yesterday. In other words, its undoing the updates previously applied. This being the case, you'd probably need to add a further date filter to your where clause making sure you only updated the data for the current day.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

rusmanicai
Starting Member

13 Posts

Posted - 2012-02-01 : 13:41:13

Actually, I added the "<" today, so I can manually run the query and update the prices. The restriction used to be
"(MaterialOnHand.CreateDatim=GETDATE() or MaterialOnHand.Updatedatim=GETDATE())" so that it would update only materials that were accessed the current day, and not the ones from the previous days. What happens is that over night, when the job runs, the field "Materialonhand.UnitCost" gets updated with the same value, no matter what value field "MaterialOnHand.Width" has in it. Yet, somehow if I manually run the same query, it updates the prices correctly (it puts a different price for every material width like it should).

The syntax inside the job step is "EXEC [dbo].[Sp_MSI_UpdateUnitCost]" (i forgot to paste that in my original post)

RJulia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:44:29

when job runs since condition is < GETDATE() wont it affect all records which are older than today (ie even ones which have been updated during yesterdays run)? i think thats why its overwriting updated values.
when will job be running each day?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rusmanicai
Starting Member

13 Posts

Posted - 2012-02-01 : 14:16:09

That is correct Visakh and it's something I don't want to do in the future, but today I had to change the = into a <= so I can update all records with proper pricing. Moving forward I will revert to =. I just have to figure out why running the SP returns the correct values and why running teh SP through a job will put the same value in all records (for same material but different width).

RJulia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 10:27:52

quote:
Originally posted by rusmanicai

That is correct Visakh and it's something I don't want to do in the future, but today I had to change the = into a <= so I can update all records with proper pricing. Moving forward I will revert to =. I just have to figure out why running the SP returns the correct values and why running teh SP through a job will put the same value in all records (for same material but different width).

RJulia

depends on when you're running SP manually and when job gets called. if job runs early morning next day, it will affect even records you update during previous day due to < logic

I think you should have a date condition based on interval >= and <

ie if job runs some day and you want to pick up only that days data then use


WHERE datefield >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
AND datefield < DATEADD(day,DATEDIFF(day,0,GETDATE())+1,0)

and if run early morning next day you need this

WHERE datefield >= DATEADD(day,DATEDIFF(day,0,GETDATE())-1,0)
AND datefield < DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rusmanicai
Starting Member

13 Posts

Posted - 2012-02-07 : 09:53:09

It worked!!!!!!!!!!!!! Thanks so much

RJulia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources