Strored Proc that checks time & sends 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 2000 Forums

SQL Server Development (2000)

Strored Proc that checks time & sends email

Author

Topic

Oleg123
Starting Member

5 Posts

Posted - 2006-04-04 : 09:59:45

i need smng in those lines --
i don't know what send email syntaxis should look like

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE DDD1
AS

DECLARE @DateDif Datetime
Declare @LastDate Datetime
Declare @Date3 Datetime

set @LastDate = 'select dest_commit_time from rs_lastcommit where origin = 103'
set @Date3 = Date

set @DateDif = DateDiff(hh,@LastDate,@Date3)

If @DateDiff >= 2 Then

SendEmail to Oleg@ddd.com , topic "More then 2hr diff"

End If

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-04 : 10:12:31

check this out..

http://www.sqlteam.com/item.asp?ItemID=5003

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 10:16:58

Also search for mail in this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

Oleg123
Starting Member

5 Posts

Posted - 2006-04-04 : 10:23:50

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE DDD1
AS

DECLARE @DateDif Datetime
Declare @LastDate Datetime
Declare @Date3 Datetime

set @LastDate = 'select dest_commit_time from rs_lastcommit where origin = 103'
set @Date3 = Date

set @DateDif = DateDiff(hh,@LastDate,@Date3)

If @DateDif >= 2 Then

EXEC sp_SQLSMTPMail

@vcTo = 'oleg@x.com,
@vcBody = 'Difference Exceeds 2 hrs.',
@vcSubject = 'FLASH PNL – Alert – Difference Exceeds 2 hrs.',
@vcSMTPServer = '10.246.2.145' ,
@vcSenderName = 'oleg@x.com'
GO

End If

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-- ok i found we already have stored proc for sending emails; i incorporated it; however am still getting errors on the word "then" and "end"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 10:27:36

your if block should be

If @DateDif >= 2

Begin
EXEC sp_SQLSMTPMail

@vcTo = 'oleg@x.com,
@vcBody = 'Difference Exceeds 2 hrs.',
@vcSubject = 'FLASH PNL – Alert – Difference Exceeds 2 hrs.',
@vcSMTPServer = '10.246.2.145' ,
@vcSenderName = 'oleg@x.com'
GO
End

Madhivanan

Failing to plan is Planning to fail

Oleg123
Starting Member

5 Posts

Posted - 2006-04-04 : 10:38:18

hi madhivanan

----
set @DateDif = DateDiff(hh,@LastDate,@Date3)

If @DateDif >= 2

Begin
EXEC sp_SQLSMTPMail @vcTo = 'louise@x.com',
@vcBody = 'Yesterday was not a bank business day.',
@vcSubject = 'RiskPnL IR Import Message - Yesterday was not a bank business day',
@vcSMTPServer = '10.246.2.145' ,
@vcSenderName = 'oleg@x.com'

GO
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------

i still get --

Server: Msg 170, Level 15, State 1, Procedure DDD1, Line 23
Line 23: Incorrect syntax near 'oleg@x.com'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'End'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-04 : 10:40:43

I think End should be before keyword GO

Madhivanan

Failing to plan is Planning to fail

Oleg123
Starting Member

5 Posts

Posted - 2006-04-04 : 10:44:06

@vcSenderName = 'oleg@x.com'
--GO
End

i was just about to ask if that "go can be commented :)
let me try now

Oleg123
Starting Member

5 Posts

Posted - 2006-04-04 : 13:57:54

had to make some changes, but seems to be working fine now.
how can i make it put ! (exclamation point) left of the message in Lotus Notes ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 01:47:51

>>how can i make it put ! (exclamation point) left of the message in Lotus Notes ?

If this is related to Lotus Notes, then try to post at relevent forum. This is MS SQL Server forum

Madhivanan

Failing to plan is Planning to fail

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-04-05 : 12:21:05

"how can i make it put ! (exclamation point) left of the message in Lotus Notes ?"

If your stored procedure allows you to set the message priority,
just set it to "High".

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources