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

SQL Server Administration (2000)

Help with smtp mail

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-08-22 : 14:24:10

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

igorblackbelt
Constraint Violating Yak Guru

407 Posts

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

This is the admin forum, I would close this one and ask on the TSQL one.

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=12

---
"There's no sexy way to carry a lunch bag." My boss

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources