SQL Job: How to start with ?

Question

Can anyone help me to create an SQL job in SQL server Agent (SQL 2008) ,which will run in a purticular time interval(Ex: Daily) and select records from a table with status=1 (select name,age from student)and pass to another stored procedure which accepts student name and age

Answer 1

Here is the approach I would take:

Create the script

• Create a SQL script with a CURSOR in it (the only reason I say cursor is because you are passing Student Name and Age to a different stored procedure)
• Read the StudentName and Age into @variables
• Execute the stored proc with appropriate parameters
• Fetch next row and loop
• IMP: TEST the script

Save the script in a SQL file for further reference.

In SQL Server Agent

• Create a new job
• point it to the appropriate database
• Paste the SQL Script (from above) into the script area of the job
• Create an appropriate schedule (daily, at 3:15 am)
• If operators and SQL Mail are setup, add those so that you can get email notifications
• Save the job
• IMP: TEST the job

Answer 2

Hi Shyju.

I think you best solution is to call a stored proceudre from the Agent with fixed arguments. Then inside this stored procedure you can loop like this: execute a stored procedure over a set without using a cursor