Help with smtp mail - SQL Server Forums

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

Transact-SQL (2000)

Help with smtp mail

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-22 : 15:04:41

Hello

I have an script for smtp mail and the query from which I need to get the out . Like the query is know whether is database is online or offline and send me a message through smtp. Can anybody help me out.

The script for the smpt I am using is :

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'Testsql2000@domain',
@FROM_NAME = N'Testsql2000',
@TO = N'd@domain',

@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@type = N'text/plain',
@message = N'status of sql server',
@messagefile= N'',
@attachment = N'',
@attachments= N'',
@codepage = 0,
@timeout = 10000,
@server = N'smtp.depaul.edu'
select RC = @rc
go

-- ************************************************************************
-- using variables
-- ************************************************************************
declare @FROM NVARCHAR(4000),
@FROM_NAME NVARCHAR(4000),
@TO NVARCHAR(4000),
@CC NVARCHAR(4000),
@BCC NVARCHAR(4000),
@priority NVARCHAR(10),
@subject NVARCHAR(4000),
@message NVARCHAR(4000),
@type NVARCHAR(100),
@attachments NVARCHAR(4000),
@codepage INT,
@rc INT
select @FROM = N'testsql2000@domain',
@FROM_NAME = N'Test sqlserver',
@TO = N'd@domain',

@priority = N'High',
@subject = N'SQL Server SMTP mail',
@message = N'<HTML><H1>Status of sql server</H1></HTML>',
@type = N'text/html',
@attachments = N'',
@codepage = 0

exec @rc = master.dbo.xp_smtp_sendmail
@FROM = @FROM,
@TO = @TO,
@priority = @priority,
@subject = @subject,
@message = @message,
@type = @type,
@attachments = @attachments,
@codepage = @codepage,
@server = N'smtp.depaul.edu'

select RC = @rc
go

-- ************************************************************************
-- ping server
-- ************************************************************************
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@server = N'mail.sqldev.net',
@port = 25,
@ping = 1
select RC = @rc
go

The query to find out the status of DB is :

SELECT DatabasePropertyEx('Your_DB_Name','Status')

So how do I link these 2 , to get a message in my mail box

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-22 : 19:29:44

if DatabasePropertyEx('Your_DB_Name','Status') = 'statusOnWhichYouNeedToSendTheMailFor'
begin
-- send mail code
end

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources