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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Posting Yak Master

120 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  
 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.08 seconds. Powered By: Snitz Forums 2000