| Author |
 Topic  |
|
|
sebastian11c
Posting Yak Master
125 Posts |
 Posted - 11/28/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/28/2012 : 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
125 Posts |
Posted - 11/28/2012 : 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
Starting Member
43 Posts |
Posted - 11/28/2012 : 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
|
|
|
| |
Topic |
|
|
|
run a query everyday at 8am and send email
