Post reply

SQL Job

  • April 20, 2010 at 12:02 am

    #156883

    Hi All

    I need help, i have a job i created for test purposes using database mail, so i had to change few things on the job then i disabled the job including the scheduling but i still get alerts even if i have deleted the job, why that?

    Please help

    Tx!

    It's better to fail while trying, rather than fail without trying!!!

  • April 20, 2010 at 2:30 am

    #1154388

    1. Where did you setup the Alert?

    2. What version suppose 2005

    3. Check - select * from sysalerts in msdb

    4. Is it perhaps logshipping

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • April 20, 2010 at 2:39 am

    #1154390

    Tx, it's a sql script that execute a database mail command if the condition is not met, then sends me an email scheduled as a job but now the job is deleted but i dont understand why i'm still getting mail. i've restarted sqlagent but still the same.

    It's better to fail while trying, rather than fail without trying!!!

  • April 20, 2010 at 3:27 am

    #1154414

    Try this script and see if the job is still in the table:

    select * from sysjobs

    What kind off condition are you looking at. Here I am thinking that there could be another alert setup in side a Job.

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • April 20, 2010 at 3:33 am

    #1154418

    Pls see below script but was scheduled as a job to run every hour

    DECLARE @CallPresentIndicator Bit -- bit data type is to indicate true or false. 0 is "False" and 1 is "True"

    IF (SELECT COUNT (*) FROM TRANS

    WHERE CALL_START >= DATEADD(HH,-1,GETDATE())

    AND CALL_START <= GETDATE ()) >0

    BEGIN

    SET @CallPresentIndicator = 1 -- this means TRUE

    PRINT 'IT IS WORKING'

    END

    ELSE

    -- Create the body of the email

    IF @CallPresentIndicator = 1

    BEGIN

    DECLARE @body1 nvarchar(2000)

    SET @body1 =

    '<html>

    <head>

    </head>

    <body style="background: #ffffff; color: #003399">

    <table border="1" align="center" cellpadding="3" width="500">

    <tr>

    <td align="center" colspan="2" style="font-size:24"><b>GIS</b></td>

    </tr>

    <tr>

    <td align="center" colspan="2" style="font-size:16;color:LIME"><b>Automated Database Mail Alert</b></td>

    </tr>

    <tr>

    <td align="left">DATE:</td>

    <td align="center">' + convert(char(19),getdate(),20) + '</td>

    </tr>

    <tr>

    <td align="left">SERVER:</td>

    <td align="center">' +@@SERVERNAME+ '</td>

    </tr>

    <tr>

    <td align="left">SEVERITY:</td>

    <td align="center" style="color:RED"><b>WARNING!</b></td>

    </tr>

    <tr>

    <td align="left">SUBSYSTEM:</td>

    <td align="center">Database Update</td>

    </tr>

    <tr>

    <td align="left">DESCRIPTION:</td>

    <td align="center">

    Please check your Application

    </td>

    </tr>

    </table>

    </body>

    </html>'

    -- Concatenate subject

    DECLARE @subj nvarchar(50)

    SET @subj = @@SERVERNAME + N': Check DB'

    -- send the email

    EXEC msdb.dbo.sp_send_dbmail @recipients='email',

    @subject = @subj,

    @body = @body1,

    @body_format = 'HTML',

    @profile_name = 'DBA Admin Profile'

    END

    It's better to fail while trying, rather than fail without trying!!!

  • April 20, 2010 at 4:02 am

    #1154433

    Hi

    The next step is to check the sysprocess table. Look for something like "SQLAgent - TSQL JobStep............"

    "select * from sysprocesses where spid > 50"

    dbcc inputbuffer(spid) - to see code that is running in the process.

    If you find the code then you can kill that process.

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • April 20, 2010 at 4:11 am

    #1154436

    Sorry here is the full select

    use master

    select * from sysprocesses where spid > 50 and program_name like 'SQLAgent - TSQL JobStep%'

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • April 20, 2010 at 5:59 am

    #1154479

    Tx 4 yr help i'm sorted

    It's better to fail while trying, rather than fail without trying!!!

  • April 20, 2010 at 7:32 am

    #1154567

    smthembu (4/20/2010)

    Tx 4 yr help i'm sorted

    If you solved your problem, it would be nice if you posted what you did. Someone else may have a similar problem and knowing what someone else did could be helpful.

  • April 20, 2010 at 7:47 am

    #1154576

    I used tvantonder-992012 suggestions as posted, also checked the msdb.dbo.sysjobactivity table and i could see the job also joined the sysjob table to get the full desc of the job id's.

    It's better to fail while trying, rather than fail without trying!!!

Viewing 10 posts - 1 through 9 (of 9 total)

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