Search code examples
sql-server-2008sql-server-agentsql-server-job

SQL Job: How to start with?

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

Solution

  • 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