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

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
websssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
websssAuthor Commented:
Thanks
Does this code create a SQL Agent job ?
sorry, but i've never used the agent before
0
QPRCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

websssAuthor Commented:
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
Anthony PerkinsCommented:
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
websssAuthor Commented:
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
Anthony PerkinsCommented:
It was not what I had in mind, but that should work, too.
0
Scott PletcherSenior DBACommented:
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
websssAuthor Commented:
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
websssAuthor Commented:
Here is the permissions for that user
permiss
0
websssAuthor Commented:
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
Scott PletcherSenior DBACommented:
Again, always code the full path to the send mail proc:

 EXEC msdb.dbo.sp_send_dbmail
0
websssAuthor Commented:
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
websssAuthor Commented:
The runas dropdown is empty
does this matter?
runas1
0
websssAuthor Commented:
Here is the permissions for the user on MSDB
dbo
0
websssAuthor Commented:
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
websssAuthor Commented:
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
Anthony PerkinsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.