Backup completion Notification using smtp

#1

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#2

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#3

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#4

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#5

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#6

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#7

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#8

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#9

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku

#10

I am taking a guess you are using SQL Server 2000.

Try using XPSMTP. http://www.sqldev.net/xp/xpsmtp.htm

It is alot more simplier and you don't have to have Outlook installed on the
server. Works without issue with SP3, SP3a, SP4.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

"opokad06" wrote:

Quote:

Also I am trying to sending backup notification using smtp.
This is the code I am using..

-------------------------------------

CREATE PROCEDURE [dbo].[Sample_sp_send_cdosysmail]

@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "

/******************************************

This stored procedure takes the parameters and
sends an e-mail. All the mail configurations are
hard-coded in the stored procedure. Comments are
added to the stored procedure where necessary.
References to the CDOSYS objects are at the following
MSDN Web site: http://msdn.microsoft.com/library/default.asp?url=/
library/en-us/cdosys/html/_cdosys_messaging.asp

*******************************************/

AS Declare @iMsg int Declare @hr int Declare @source varchar(255) Declare
@description varchar(500) Declare @output varchar(1000)

--***** Create the CDO.Message Object *****

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--*****Configuring the Message Object *****

-- This is to configure a remote SMTP server.
--
http://msdn.microsoft.com/library/de..._sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields
("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.
microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.va.gov'

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

I did change the Mail Server name to the smtp server name but I go not get
any mail messages when I execute the stored procedure.

Sample_sp_send_cdosysmail] 'test (AT) aol (DOT) com', 'test2 (AT) aol (DOT) com', 'testing mail
notification', 'that's cool'

What am I doing wrong? response greatly appreciated.

--
KPoku