Solved

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

Posted on 2014-07-28
18
684 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
  • 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
 

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:ScottPletcher
ScottPletcher 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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:ScottPletcher
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now