Post reply

SQL Server 2005 Job

  • October 2, 2008 at 3:25 pm

    #198812

    I have been approached to start an internal audit of our logins table. This table shows who is logged in and using a license in our system. I have built the select statements and can get the information manually, but I would much rather setup a job to automatically do this. Any ideas on how I could accomplish this? Any help will be greatly appreciated.

  • October 2, 2008 at 5:01 pm

    #880228

    1. Expand SQL Server Agent in SQL Server Management Studio

    2. Right-click on Jobs and select New Job...

    3. On the General page, give the job a name

    4. On the Steps page, click the New... button

    5. Give the step a name, select "T-SQL" for the type, select the database to run the statements in, type or paste the statements, click OK

    6. On the Schedules page, click the New... button

    7. Set the schedule details and click OK

    8. On the Notifications page, set any actions to perform when the job completes and click OK

    9 Click OK to create the job

    This is all documented in BooksOnLine if you need details about any of the steps.

    Greg

  • October 3, 2008 at 7:05 am

    #880436

    I would like the count of licenses in use, returned from the script, to be saved in a log file or something for viewing. Will the instructions you sent cover this?

  • October 3, 2008 at 10:44 am

    #880594

    You'd have to do that as part of your queries. You could insert the selected data into a table or use sp_send_dbmail to send an email message with the query results attached.

    Greg

  • October 30, 2008 at 7:59 am

    #891800

    Sorry for such a delayed response but I have been pulled off every project I start. I am now back to this again. Here is what I have, setup on a job, but it fails each time. Do you see anything out of place with the script?

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Administrator',

    @recipients = 'administrator@enviro-ok.com',

    @query = 'SELECT COUNT(USER_ID) AS LICENSE_COUNT FROM VMFGSNAP.LOGINS

    WHERE PROGRAM_ID = 'MENU'',

    @subject = 'LICENSE AUDIT',

    @attach_query_result_as_file = 1 ;

  • November 11, 2008 at 3:45 pm

    #897202

    I figured it out.

    Thanks for all the help and guidance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply