run a query everyday at 8am and send email

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

run a query everyday at 8am and send email

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-11-28 : 10:32:37

hi there
one more time i need your help

i need a query to verify everyday if there are some bill that arent payed ,after that i need to send a email using database email with the information about the unpayed bills

create a table

CREATE TABLE [dbo].[bill](
[idbill] [int] NULL,
[alreadypayed] [bit] NULL
) ON [PRIMARY]

GO

lets put some records on it

INSERT INTO [dbo].[bill]
([idbill]
,alreadypayed)
select 1, 1
union
select 2, 0
union
select 3, 0
union
select 4, 1
union
select 7, 1

and thats what i need.
i need to run automatically everyday at 8am this query:

if exists ( bills that aren already payed)
send an email with this information

(select * from bill where alreadypayed=0)

many many thanks in advanced for your help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-28 : 10:51:41

Yes you can have the logic stored in table and based on flag you can send email to recipients. Use sp_send_dbmail. Make sure you don't send multiple times.

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-11-28 : 11:07:39

hi sodeep
thanks for your answer, but i dont know how to achieve this, i dont know how to run automatically a sp and after sending an email

please help me

mandm
Posting Yak Master

120 Posts

Posted - 2012-11-28 : 18:57:06

Here is a sample of something similar to what you want to do. The red text areas need to be modified for Profile and Email Recipients. You can take a proc like this and set it up in a SQL Agent job to run at whatever schedule you like.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCheckReplicationErrors]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spCheckReplicationErrors]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spCheckReplicationErrors]

AS

DECLARE @MailProfile VARCHAR(100)
DECLARE @UserEmail VARCHAR(8000)
DECLARE @EmailUserText VARCHAR(8000)
DECLARE @Priority VARCHAR(20)
DECLARE @UserSubjectLine VARCHAR(500)
DECLARE @ErrorMessage VARCHAR(8000)
DECLARE @ErrorID INT
DECLARE @MinutesSince INT
DECLARE @ErrorTimeRecency INT

SET @MailProfile = 'DbMail Profile Name'
SET @UserEmail = 'Email Recipients'
SET @EmailUserText = 'Replication Errors Detected Within The Last '
SET @Priority = 'High'
SET @UserSubjectLine = 'Replication Errors Detected'
SET @ErrorTimeRecency = 10

SET NOCOUNT ON

SELECT @ErrorID = me.id
, @MinutesSince = DATEDIFF(MI, me.[time], GETDATE())
FROM distribution.dbo.msrepl_errors AS me WITH (NOLOCK)
JOIN (
SELECT MAX(id) AS MaxRowID
, MAX([time]) AS MaxErrDateTime
FROM distribution.dbo.msrepl_errors WITH (NOLOCK)
WHERE error_code = '20598'
AND CONVERT(NVARCHAR(MAX), error_text) = 'The row was not found at the Subscriber when applying the replicated command.'
) AS mx
ON me.id = mx.MaxRowID
AND me.[time] = mx.MaxErrDateTime

IF @MinutesSince <= @ErrorTimeRecency
BEGIN

SET @EmailUserText = @EmailUserText + CONVERT(VARCHAR(500), @MinutesSince) + ' minutes.'

--SELECT @EmailUserText

-- Email IT
EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile, @recipients = @UserEmail, @body = @EmailUserText, @subject = @UserSubjectLine, @importance = @Priority

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources