Solved

sql agent - check table every 30 mins - if no data in last 30 mins send email

Posted on 2014-07-28
18
762 Views
1 Endorsement
Last Modified: 2014-08-04
Hi
I wonder if someone can help me
i've enabled database mail on sql 2014
I have set the sql server agent to run on startup

Next I need to execute this query
SELECT count (*)
  FROM tblCommonTrackingData
  where dGPSDateTime <   CURRENT_TIMESTAMP and dGPSDateTime > DateADD(minute, -90, GETDATE())

Open in new window


If it = 0 then I need to send an email saying the import EXE has crashed

Can anyone help me with this please?
EDIT: yes i realise the script says 90 mins, lets stick with 90
1
Comment
Question by:websss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 3
  • 2
  • +1
18 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 250 total points
ID: 40226051
declare @recount int
select @recount = count(*) from etc

if @recount < 1
USE [msdb]
    EXEC sp_send_dbmail
      @profile_name = 'MailProfile1',
      @recipients = 'your@email.com',
      @subject = 'import exe has crashed',
      @body = 'some sample text in here'
0
 

Author Comment

by:websss
ID: 40226108
Thanks
Does this code create a SQL Agent job ?
sorry, but i've never used the agent before
0
 
LVL 29

Expert Comment

by:QPR
ID: 40226121
No you create a new job and put this code in under the first step. Then schedule it for a start time and repeat every 30 minutes
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:websss
ID: 40226646
Thanks
I've done that but now have the error
Message
Executed as user: webapplication. Could not find stored procedure 'sp_send_dbmail'. [SQLSTATE 42000] (Error 2812).  The step failed.

I've added this user to the MSDB users table and is part of the following roles
DatabaseMailUserRole
Db_Datareader
Db_Datawriter
DB_owner
SQLAGENToperatorRole
SQLAGENTReaderRole
SQLAGENTUserROle

but it still failed with the above error
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40226654
Make sure the Job Step refers to the msdb database or reference the msdb database explicitly as in:
EXEC msdb.dbo.sp_send_dbmail ...
0
 

Author Comment

by:websss
ID: 40226662
declare @recount int
select @recount = count (*)
  FROM tblCommonTrackingData
  where dGPSDateTime <   CURRENT_TIMESTAMP and dGPSDateTime > DateADD(minute, -10, GETDATE())

if @recount < 1
USE [msdb]
    EXEC sp_send_dbmail
      @profile_name = 'myProfile',
      @recipients = 'me@me.co',
      @subject = 'import exe has crashed',
      @body = 'some sample text in here'

Open in new window


Like this?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40226801
It was not what I had in mind, but that should work, too.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40227214
There's no reason to count all the rows since you only want to see if one exists.  NOT EXISTS will be much more efficient, particularly if a lot of emails have been added during that time:


DECLARE @minutes_interval int
SET @minutes_interval = ABS(10)

IF NOT EXISTS(
    SELECT 1
    FROM tblCommonTrackingData
    WHERE
        dGPSDateTime <  GETDATE() AND
        dGPSDateTime > DATEADD(minute, -@minutes_interval, GETDATE())
)
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'myProfile',
      @recipients = 'me@me.co',
      @subject = 'import exe has crashed',
      @body = 'some sample text in here'    
END --IF
0
 

Author Comment

by:websss
ID: 40228526
Thanks Scott
Will definatley use that code soon

however, the first issue I need to fix is the job is failing with error

Message
Executed as user: webapplication. Could not find stored procedure 'sp_send_dbmail'. [SQLSTATE 42000] (Error 2812).  The step failed.
0
 

Author Comment

by:websss
ID: 40228531
Here is the permissions for that user
permiss
0
 

Author Comment

by:websss
ID: 40228535
And here is the create script in case it helps
 
USE [msdb]
GO

/****** Object:  Job [CheckDataStillComingIn]    Script Date: 30/07/2014 07:14:09 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 30/07/2014 07:14:09 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'CheckDataStillComingIn', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'check to see if the data is still coming into the database', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Check DB has data recently]    Script Date: 30/07/2014 07:14:10 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check DB has data recently', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'declare @recount int
select @recount = count (*)
  FROM tblCommonTrackingData
  where dGPSDateTime <   CURRENT_TIMESTAMP and dGPSDateTime > DateADD(minute, -10, GETDATE())

if @recount < 1
USE [msdb]
    EXEC sp_send_dbmail
      @profile_name = ''myProfile'',
      @recipients = ''me@me.co'',
      @subject = ''import exe has crashed'',
      @body = ''some sample text in here''', 
		@database_name=N'GPSOL', 
		@database_user_name=N'webapplication', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DataInDB', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=30, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20140729, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'b3402cbf-9123-43fe-9ca3-29bb1c32b801'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40229163
Again, always code the full path to the send mail proc:

 EXEC msdb.dbo.sp_send_dbmail
0
 

Author Comment

by:websss
ID: 40233477
thanks
i did that but same error
i tried changing the script to run as SA but it now says

Msg 14262, Level 16, State 1, Procedure sp_verify_jobstep, Line 336
The specified @database_user_name ('sa') does not exist.
0
 

Author Comment

by:websss
ID: 40233487
The runas dropdown is empty
does this matter?
runas1
0
 

Author Comment

by:websss
ID: 40233502
Here is the permissions for the user on MSDB
dbo
0
 

Author Comment

by:websss
ID: 40233503
Got a little further
Now the error is
Message
Executed as user: webapplication. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229).  The step failed.

The above image shows the permissions of this user
0
 

Author Comment

by:websss
ID: 40233557
ok I fixed it


first did this

ALTER DATABASE GPSOL SET TRUSTWORTHY ON
Then did this
-----------------------
--if the below 2 lines have a different user you must run
--ALTER AUTHORIZATION ON Database::GPSOL TO [sa]

--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'
--------------------------------

--then based on above result did this
ALTER AUTHORIZATION ON Database::GPSOL TO [sa]

However, i'm getting the emails regardless of the COUNT
the count is approx 1000+ but its still sending the email

see code here:
declare @recount int
USE GPSOL
select @recount = count (*)
  FROM tblData
  where dGPSDateTime <   CURRENT_TIMESTAMP and dGPSDateTime > DateADD(minute, -10, GETDATE())

if @recount < 1
USE [msdb]
    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'myProfile',
      @recipients = 'me@w.com',
      @subject = 'import exe may of has crashed - data has stopped coming in',
      @body = 'blah'

Open in new window


any ideas why the email still sends ?

running this statement
USE GPSOL
select count (*)
  FROM tblData
  where dGPSDateTime <   CURRENT_TIMESTAMP and dGPSDateTime > DateADD(minute, -10, GETDATE())

Open in new window

gets over 1000  records at the time of the email
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40234388
Comment out the line USE [msdb] it is pointless.  If you cannot bring yourself to do that, then you will have to change the code like this

IF @recount < 1
    BEGIN
     USE [msdb]
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myProfile', @recipients = 'me@w.com', @subject = 'import exe may of has crashed - data has stopped coming in',
    @body = 'blah'
    END    

Or:
USE [msdb]
IF @recount < 1
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myProfile', @recipients = 'me@w.com', @subject = 'import exe may of has crashed - data has stopped coming in',
    @body = 'blah'
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question