Automate SQL Query/SP

[Kirbydog]

Hello fellow programmers,

I have a situation that I need help with, I have a VB front end and a MS-SQL back end. I need to automate an update query to run once every 24 hours in order to track some inventory items. A sample of my query is as follows:

UPDATE Tools03 SET TransCode = 3, TransDesc = 'Missing', StatusDate = CONVERT(char(30), GETDATE(), 1),LastUpdate_ID = 'TRM',LastUpdate_ts = CONVERT(char(30), GETDATE(), 1),Time = CONVERT(char(30), GETDATE(), 8)
WHERE TransCode = 1 AND Statusdate = '7/7/2004'

Here is what I am trying to accomplish:

1. Automate the above query to once every 24 hours.
2. I need to calulate in my where clause, StatusDate = (Today's date - 5 days).

The above is needed because what it does is to change items in 1 field so that they can populate several reports.

Any help or light that you can shed on this matter is appreciated, thank you.

James

 

[Krickles]

Set the query to a job:

Enterprise Manager --> Management --> SQL Server Agent --> Jobs --> Right click in white are of main window and choose new job --> Fill in the tabs (your query can be pasted as a step under the Steps tab by click new)

In the where clause use StatusDate = getdate()-5.

Krickles | 1.6180

 

[Kirbydog]

Thanks Krickles,

I applied your solutions this morning but I won't be able to tell you anything until tomorrow morning when I get in. I created the job to run at 12:00 AM, 1:00 AM and 2:00 AM PST so we will see what happens, lets keep our fingers crossed, (X) .

James

 

[Krickles]

I'm glad the info helped. I didn't think to mention it earlier, but when I test new jobs I turn on the email when fail option for the first month (Notification tab) becuase a lot of the jobs I set up deal with end of month closing. You may want to do the same...it's a nice feature.

Krickles | 1.6180

 

[Kirbydog]

Will do, thanks...

James

 

[Kirbydog]

Good Morning,

Well the query did not work for the date addition/subtraction portion of the where clause. For some reason SQL 7 cannot perform date addition or subtraction.

I tried looking in the SQL on-line books but could not find anything on subtracting dates in a where clause and, "GETDATE() - 5" just will not work. Back to the drawing board...

Does anyone have any suggestions?

James

 

[Glasgow]

Have you considered using the DateDiff function to do the subtraction?

 

[Kirbydog]

Yes, I tried the following, DATEDIFF(day,5,StatusDate) where day is the DATE, 5 is the number to subtract so that I get to a day that was five days ago and StatusDate is the field that I want to select all days that meet this criteria.

In VB it can be done with no problem but in SQL 7 I created a job but the query does not calulate the date and therfore does not work... :-(

James

 

[Kirbydog]

Thanks everyone for your help, I was able to resolve the date addition/subtraction issue by the following syntax:

StatusDate = CONVERT(DATETIME,CONVERT(varchar(12),GETDATE())) - 6

Which put the date portion into an interger format which allowed for the subtraction, again thanks for your help...

James