xp_sendmail - 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)

xp_sendmail

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-05 : 14:30:37

Ok I can setup a sendmail in Q.A. and it works. now what do I have to do to it so that I set it in a S.P.

Jim
Users <> Logic

Shastryv
Posting Yak Master

145 Posts

Posted - 2003-06-05 : 14:34:34

There are several things to monitor and report either to On-Call or Lead, What is the main req in your case? Here is a link that i wrote 2 Procs, might be useful to you

http://www.sqlservercentral.com/scripts/contributions/819.asp

http://www.sqlservercentral.com/scripts/contributions/817.asp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-05 : 14:50:18

I would recommend not using xp_sendmail in a stored procedure. Instead, just insert the e-mail into a table. Then you can either create a trigger on insert that does the xp_sendmail or create a SQL job that periodically checks for records in the table and then e-mails them, then deletes the records.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-05 : 15:05:51

This will eventally be used to create mass mailings from stored SQL tables.

ALTER PROCEDURE dbo.StoredProcedure1
AS

EXEC master..xp_sendmail @recipients = '
SELECT EAddress
FROM dbo.emailtest' ,
@message = 'test of Sp'

Returns "Could not resolve recipient"

EAddress contains only one email address - mine
I must be screwing up the syntax

Jim
Users <> Logic

Edited by - jiml on 06/05/2003 15:10:01

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-05 : 16:13:19

You need to SELECT from the table first. Put the value into a variable. Then set @recipient to this variable.

So, here it is:

ALTER PROCEDURE dbo.StoredProcedure1
AS

DECLARE @email VARCHAR(100)

SELECT @email = EAddress
FROM dbo.emailtest

EXEC master..xp_sendmail @recipients = @email, @message = 'test of Sp'

Keep in mind that this will only work for one record. But since you only have one record in that table, this will work for now. Once you add more records, you are going to have to loop through the table and e-mail each recipient. You can accomplish this with a WHILE statement and an incrementing column in the table.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-06 : 08:30:22

Thanks Tarra
I thought I had tried that already but I must have messed up somewhere. Didiint know about the single record limitation will have to get around that.

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-06 : 13:24:04

Now the sendmail works fine all the way up to adding the ,@attachments statement it now nether errors or works.

ALTER PROCEDURE dbo.loopemail
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[emailtest2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[emailtest2]

CREATE TABLE [dbo].[emailtest2] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[EAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

INSERT INTO dbo.emailtest2
(EAddress)
SELECT EAddress
FROM dbo.emailtest

Declare @Eaddress Varchar(100)
declare @EMid int
set
@emid = 1

While (Select eaddress From Dbo.emailtest2 Where (id = @emid)) Is Not Null
begin
SELECT @Eaddress = EAddress
FROM dbo.emailtest2
Where (id = @EMid)

EXEC master..xp_sendmail @recipients = @EAddress ,
@message = 'test of Sp',@attachments = 'S:\eblast\eblast.doc'
set
@emid = @emid + 1
end

Any Ideas???

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-06 : 13:27:57

Try running the xp_sendmail query in Query Analyzer instead of inside your stored procedure. Also verify that the path is correct for the attachment, although I am sure that you already did this. Let us know what you get in Query Analyzer.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-06 : 13:40:40

Ok Now I get an error (mail error 0x80004005) help in MS KB states the problem but I could not find any solutions there.

If I just sent message text it works just fine.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-06 : 13:42:58

Ah yes, the common error message for SQLMail. This is why I don't recommend using xp_sendmail. You will need to reboot the server in order to use SQLMail again. We had this problem for a long time and eventually MS recommended that we install Outlook 2002. Since then, we have not had the SQLMail problems although we still shy away from using SQLMail.

[EDIT] Hmmm, reread your post, since you can still use SQLMail, you need to verify the path. I wonder if SQLMail is able to use the S drive. Try moving the file to the C drive and run it again. [/EDIT]

Tara

Edited by - tduggan on 06/06/2003 13:44:19

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-06 : 13:51:46

Your were Rignt on the money again Tarra.I forgot to look at the pathing from the servers point of View.

Now it sends the message but still no attachment.

I wonder if Outlook is blocking it?

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-06 : 13:56:26

Wow All of a sudden it works. MS Strikes again!!!!

Thanks for all your time and paitiance Tarra you saved my fanny.

Again!!

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-06 : 13:56:50

Well Outlook wouldn't be blocking it, but your E-mail server might be. Send the e-mail again but with a different attachment, such as a notepad file. Word docs are probably being blocked on your e-mail server.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-06-06 : 14:01:59

I said Outlook didint I, I ment my exchange server.

you can tell Its friday, brain has gone into weekend mode.

Thanks again.

Its almost

Time.

To bad I quit. LOL

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources