Finding email supporting sp's - 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 Development (2000)

Finding email supporting sp's

Author

Topic

howard7890
Starting Member

7 Posts

Posted - 2002-11-01 : 20:27:55

Where do I find a copy of sp_OACreate, sp_OASetProperty, sp_OAMethod, sp_OADestroy.

I looked in the stored procedures section, and it is not there.

I am trying to send email from my server
Here is my sp:

Create Procedure sp_SMTPMail

@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = 'localhost'

AS

SET nocount on

declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress

EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body

EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

EXEC sp_OADestroy @oMail
END

SET nocount off
GO

I call it from the Query Analyzer:
declare @sn varchar
declare @sa varchar
declare @rn varchar
declare @ra varchar
declare @subject varchar
declare @body varchar

set @sn = 'Howard Kline'
set @sa = 'howard.kline@amd.com'
set @rn = 'Howard Kline'
set @ra = 'howard.kline@amd.com'
set @subject = 'Test Message'
set @body = 'It Worked!'

EXEC sp_SMTPMail @sn,@sa,@rn,@ra,@subject,@body

It runs the sp but does not send anything.
My server has outlook installed and sends email.

Any help would be great.
Thanks,

Edited by - howard7890 on 11/01/2002 20:30:46

Edited by - howard7890 on 11/01/2002 20:31:12

Edited by - Merkin on 11/01/2002 21:31:02

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-01 : 21:32:31

The edit was to put it in the right topic area.

I recognize that proc

Do you have the SMTPsvg.Mailer component installed on your server ? It is a third party component, nothing to do with outlook.

Damian

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-11-02 : 12:22:03

AS Merkin states, you are trying to instantiate a 3rd party mail component.
The sp_OA* procedures exist in the master database (if you look in EM they are in the Extended Stored Procedures folder). To send email without SQL Mail (although since you have Outlook installed you could set it up if it's not already and use xp_sendmail) check out
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;q312839[/url]
[url]http://sqldev.net/xp/xpsmtp.htm[/url]

HTH
Jasper Smith

howard7890
Starting Member

7 Posts

Posted - 2002-11-02 : 18:55:37

Hi, i am getting an error when I try to run the sproc.
Here is what I have:
CREATE PROCEDURE TempMail AS
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = 'howard.kline@amd.com',
@TO = 'howard.kline@amd.com'
select RC = @rc
GO

exec TempMail

Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'master.dbo.xp_smtp_sendmail'.
(1 row(s) affected)

What am I doing wrong?
Is there something I need to install or activate in my SQL server to get this to work.

Thanks,

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-11-02 : 19:19:35

Yes

You need to add the extended stored procedure xp_smtp_sendmail
[url]http://sqldev.net/xp/xpsmtp.htm[/url]

-- SQL Server 7.0 install
exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp70.dll'
-- SQL Server 2000 install
exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp80.dll'

HTH
Jasper Smith

howard7890
Starting Member

7 Posts

Posted - 2002-11-04 : 15:10:24

Help!

I have used both sp_SMTPMail and master.dbo.xp_smtp_sendmail.
The sprocs complete successfully but I do not receive any email.

Any Ideas.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-04 : 17:06:06

We can't help if you won't answer the simple questions we ask you.

Do you have the 3rd party components installed ?

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources