locked
Help with my first automated "job"? RRS feed

  • Question

  • Hello everyone...

    I'm a beginner, and most of my (admittedly limited) experience so far is with LINQ so I'm in very unfamiliar territory.  I hope someone can point me in the right direction and I apologize if it's asking too much but here it goes:

    I have a db which contains a table in which is a date column that represents an expiration date for that record.  I need to create an automated "job" I guess it's called in SSMS (again, if I understand correctly) that will run a query to delete records whose expiration date is past.  I had originally thought about doing this via a hidden web page in my application for admins but that seems... not the right way.

    So, at risk of pushing my luck could somebody give me some guidance in A) the correct T SQL syntax for this, and walk me through the process of installing this?  I'm a fast learner but too much of this is new to me and I can'd much documentation, especially for creating the "job".

    Thanks very very much in advance for any help!

    Thursday, March 1, 2012 2:16 PM

Answers

  • Whew... I think I muddled through it guys with your help!  I tried pasting the query into the query window and it ran so I knew that wasn't the problem.  Then I deleted the job and built a new one.  I have a suspicion I didn't correctly choose the db that was to be targeted.  This time it seemed to run fine. 

    If I'm not back, you'll know it worked... and I'm very grateful!

    All best wishes

    • Proposed as answer by Peja Tao Friday, March 2, 2012 7:08 AM
    • Marked as answer by PaulBinCT Tuesday, March 6, 2012 8:19 PM
    Thursday, March 1, 2012 5:26 PM

All replies

  • You can use the links's below to create a job using SSMS ,to create a T SQL job step under the job and to schedule a job

    http://msdn.microsoft.com/en-us/library/ms190268.aspx

    http://msdn.microsoft.com/en-us/library/ms187910.aspx

    http://msdn.microsoft.com/en-us/library/ms191439.aspx

    The same can be achieved using sp_add_job,sp_add_job_step and sp_add_job_schedule

    http://msdn.microsoft.com/en-us/library/ms182079(v=sql.90).aspx

    http://msdn.microsoft.com/en-us/library/ms187358(v=sql.90).aspx

    http://msdn.microsoft.com/en-us/library/ms366342.aspx

    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer



    • Edited by Vinay Valeti Thursday, March 1, 2012 2:53 PM added job schedule
    • Proposed as answer by Peja Tao Friday, March 2, 2012 7:08 AM
    Thursday, March 1, 2012 2:46 PM
  • Hi Paul

    The syntax you need would be something similar to this...

    DELETE FROM YourTable
    WHERE YourExpiryDate <= GETDATE()
    Hope this helps

    Jon Royales

    Thursday, March 1, 2012 4:38 PM
  •  if you are not storing time values with your dates (e.g. 2012-02-15 00:00:00) you might want to use the following code instead

    DELETE FROM YourTable 
    WHERE YourExpiryDate <= CONVERT(DATETIME,(CONVERT(VARCHAR(10),GETDATE(),120)))
    Regards
    • Edited by Jon Royales Thursday, March 1, 2012 4:49 PM error
    • Proposed as answer by Peja Tao Friday, March 2, 2012 7:08 AM
    Thursday, March 1, 2012 4:43 PM
  • Thanks very much guys... I'm definately getting there!  I wrote the query and it parsed OK but Server Agent I tried to run it and Server Agent is stopped and despite reading the info on MS on starting it, it won't.  Following the instructions, I go to Config manager, then to the server agent which shows "Stopped" and right clicking won't start it.  I go to the properties and it shows start mode is stopped, but when I make any other selection such as auto or manual I get an error that remote procedure has failed (or something along those lines).  Any suggestions?

    And thanks again!

    Thursday, March 1, 2012 4:55 PM
  • Ok... scratch that.  I think I found it buried in a Services menu and successfully started it.  Now I'll see if the queries work and report back!

    :)

    Thursday, March 1, 2012 4:59 PM
  • OK... back again.

    I tried both of your queries Jon and the job won't run although the query parses.  So, I'm not sure what's going on, needless to say ;)  The error log shows me events that all seem to be regarding email notifications although I haven't tried to set any up.  So I'm not sure if it's the query itself or something else that's not behaving.  Don't give up on me ;)

    Thursday, March 1, 2012 5:15 PM
  • Whew... I think I muddled through it guys with your help!  I tried pasting the query into the query window and it ran so I knew that wasn't the problem.  Then I deleted the job and built a new one.  I have a suspicion I didn't correctly choose the db that was to be targeted.  This time it seemed to run fine. 

    If I'm not back, you'll know it worked... and I'm very grateful!

    All best wishes

    • Proposed as answer by Peja Tao Friday, March 2, 2012 7:08 AM
    • Marked as answer by PaulBinCT Tuesday, March 6, 2012 8:19 PM
    Thursday, March 1, 2012 5:26 PM