SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 run a query everyday at 8am and send email
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sebastian11c
Posting Yak Master

129 Posts

Posted - 11/28/2012 :  10:32:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/28/2012 :  10:51:41  Show Profile  Reply with Quote
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.
Go to Top of Page

sebastian11c
Posting Yak Master

129 Posts

Posted - 11/28/2012 :  11:07:39  Show Profile  Reply with Quote
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
Go to Top of Page

mandm
Yak Posting Veteran

53 Posts

Posted - 11/28/2012 :  18:57:06  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000