how can i send mail by using sql server

hi all

my query is how can i send mail by using sql server, i think by unsing
xp_sendmail store procedure we can do this but my problem is what
paramater to be pass to this store procedure if i want to send mail
from my local machine to another user, bcos i am geeting this error
msg in query analyzer...

query:-EXEC master..xp_sendmail 'ss35934' (ss35934 is valid user name
present into my local nerwork)

getting error msg :- xp_sendmail: failed with mail error 0x80040111

from:- sachin shah

Re: how can i send mail by using sql server

On Aug 24, 7:47 am, sachin shah wrote:
> hi all
>
> my query is how can i send mail by using sql server, i think by unsing
> xp_sendmail store procedure we can do this but my problem is what
> paramater to be pass to this store procedure if i want to send mail
> from my local machine to another user, bcos i am geeting this error
> msg in query analyzer...
>
> query:-EXEC master..xp_sendmail 'ss35934' (ss35934 is valid user name
> present into my local nerwork)
>
> getting error msg :- xp_sendmail: failed with mail error 0x80040111
>
> from:- sachin shah

I had a similar problem and was advised not to use xp_sendmail. I
don't understand how sql server communicates with email. I don't know
the difference between using xp_sendmail (does not work) and sending a
notification email in the job scheduler (which does work)!

There's an alternative you could try which is available from
http://www.sqldev.net/xp/xpsmtp.htm

Re: how can i send mail by using sql server

On Aug 24, 12:57 pm, stephen wrote:
> On Aug 24, 7:47 am, sachin shah wrote:
>
> > hi all
>
> > my query is how can i send mail by using sql server, i think by unsing
> > xp_sendmail store procedure we can do this but my problem is what
> > paramater to be pass to this store procedure if i want to send mail
> > from my local machine to another user, bcos i am geeting this error
> > msg in query analyzer...
>
> > query:-EXEC master..xp_sendmail 'ss35934' (ss35934 is valid user name
> > present into my local nerwork)
>
> > getting error msg :- xp_sendmail: failed with mail error 0x80040111
>
> > from:- sachin shah
>
> I had a similar problem and was advised not to use xp_sendmail. I
> don't understand how sql server communicates with email. I don't know
> the difference between using xp_sendmail (does not work) and sending a
> notification email in the job scheduler (which does work)!
>
> There's an alternative you could try which is available fromhttp://www.sqldev.net/xp/xpsmtp.htm


hi stephen,

i tried with xp_smtp_sendmail but still getting the error ....
as given into site i register the xp_smtp_sendmail store procedure and
the assign the grant permission to that store procedure then writing
the following query

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MyEmail@MyDomain.com',
@TO = N'MyFriend@HisDomain.com'
select RC = @rc
go

in place of this 'MyEmail@MyDomain.com' i am writing the real mail id
(first one)
in place of this 'MyFriend@HisDomain.com' i am writing the real mail
id (second one)

but after executing these query getting the following error msg..

Error: connecting to server smarthost

can u plz tell me is there any sql server id we need to right into
above query (like admin@sqlserver.com)

from sachin shah

Re: how can i send mail by using sql server

> but after executing these query getting the following error msg..
>
> Error: connecting to server smarthost

'smarthost' is the default name of the SMTP server. It is very likely that
your actual SMTP server is named something else so you'll need to specify
the name using the @server parameter:

DECLARE @rc int
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MyEmail@MyDomain.com',
@TO = N'MyFriend@HisDomain.com',
@server =N'MY-SMTP-SERVER'
SELECT RC = @rc

See the xpxmtp documentation for detailed usage:
http://www.sqldev.net/xp/xpsmtp.htm


--
Hope this helps.

Dan Guzman
SQL Server MVP

"sachin shah" wrote in message
news:1187943974.754692.292770@r23g2000prd.googlegroups.com.. .
> On Aug 24, 12:57 pm, stephen wrote:
>> On Aug 24, 7:47 am, sachin shah wrote:
>>
>> > hi all
>>
>> > my query is how can i send mail by using sql server, i think by unsing
>> > xp_sendmail store procedure we can do this but my problem is what
>> > paramater to be pass to this store procedure if i want to send mail
>> > from my local machine to another user, bcos i am geeting this error
>> > msg in query analyzer...
>>
>> > query:-EXEC master..xp_sendmail 'ss35934' (ss35934 is valid user name
>> > present into my local nerwork)
>>
>> > getting error msg :- xp_sendmail: failed with mail error 0x80040111
>>
>> > from:- sachin shah
>>
>> I had a similar problem and was advised not to use xp_sendmail. I
>> don't understand how sql server communicates with email. I don't know
>> the difference between using xp_sendmail (does not work) and sending a
>> notification email in the job scheduler (which does work)!
>>
>> There's an alternative you could try which is available
>> fromhttp://www.sqldev.net/xp/xpsmtp.htm
>
>
> hi stephen,
>
> i tried with xp_smtp_sendmail but still getting the error ....
> as given into site i register the xp_smtp_sendmail store procedure and
> the assign the grant permission to that store procedure then writing
> the following query
>
> declare @rc int
> exec @rc = master.dbo.xp_smtp_sendmail
> @FROM = N'MyEmail@MyDomain.com',
> @TO = N'MyFriend@HisDomain.com'
> select RC = @rc
> go
>
> in place of this 'MyEmail@MyDomain.com' i am writing the real mail id
> (first one)
> in place of this 'MyFriend@HisDomain.com' i am writing the real mail
> id (second one)
>
> but after executing these query getting the following error msg..
>
> Error: connecting to server smarthost
>
> can u plz tell me is there any sql server id we need to right into
> above query (like admin@sqlserver.com)
>
> from sachin shah
>

Re: how can i send mail by using sql server

On Aug 24, 5:06 pm, "Dan Guzman" online.sbcglobal.net> wrote:
> > but after executing these query getting the following error msg..
>
> > Error: connecting to server smarthost
>
> 'smarthost' is the default name of the SMTP server. It is very likely that
> your actual SMTP server is named something else so you'll need to specify
> the name using the @server parameter:
>
> DECLARE @rc int
> EXEC @rc = master.dbo.xp_smtp_sendmail
> @FROM = N'MyEm...@MyDomain.com',
> @TO = N'MyFri...@HisDomain.com',
> @server =N'MY-SMTP-SERVER'
> SELECT RC = @rc
>
> See the xpxmtp documentation for detailed usage:http://www.sqldev.net/xp/xpsmtp.htm
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "sachin shah" wrote in message
>
> news:1187943974.754692.292770@r23g2000prd.googlegroups.com.. .
>
>
>
> > On Aug 24, 12:57 pm, stephen wrote:
> >> On Aug 24, 7:47 am, sachin shah wrote:
>
> >> > hi all
>
> >> > my query is how can i send mail by using sql server, i think by unsing
> >> > xp_sendmail store procedure we can do this but my problem is what
> >> > paramater to be pass to this store procedure if i want to send mail
> >> > from my local machine to another user, bcos i am geeting this error
> >> > msg in query analyzer...
>
> >> > query:-EXEC master..xp_sendmail 'ss35934' (ss35934 is valid user name
> >> > present into my local nerwork)
>
> >> > getting error msg :- xp_sendmail: failed with mail error 0x80040111
>
> >> > from:- sachin shah
>
> >> I had a similar problem and was advised not to use xp_sendmail. I
> >> don't understand how sql server communicates with email. I don't know
> >> the difference between using xp_sendmail (does not work) and sending a
> >> notification email in the job scheduler (which does work)!
>
> >> There's an alternative you could try which is available
> >> fromhttp://www.sqldev.net/xp/xpsmtp.htm
>
> > hi stephen,
>
> > i tried with xp_smtp_sendmail but still getting the error ....
> > as given into site i register the xp_smtp_sendmail store procedure and
> > the assign the grant permission to that store procedure then writing
> > the following query
>
> > declare @rc int
> > exec @rc = master.dbo.xp_smtp_sendmail
> > @FROM = N'MyEm...@MyDomain.com',
> > @TO = N'MyFri...@HisDomain.com'
> > select RC = @rc
> > go
>
> > in place of this 'MyEm...@MyDomain.com' i am writing the real mail id
> > (first one)
> > in place of this 'MyFri...@HisDomain.com' i am writing the real mail
> > id (second one)
>
> > but after executing these query getting the following error msg..
>
> > Error: connecting to server smarthost
>
> > can u plz tell me is there any sql server id we need to right into
> > above query (like ad...@sqlserver.com)
>
> > from sachin shah- Hide quoted text -
>
> - Show quoted text -


hi

as per u r guide line i insatalled that xp_smtp_sendmail store
procedure and assign the grant permission to that SP but after
executing the following query its showing following error....

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'MyEmail@MyDomain.com',
@FROM_NAME = N'My Full Name',
@replyto = N'sashah@systime.net',
@TO = N'sachin28880@gmail.com',
@priority = N'NORMAL',
@subject = N'Hello SQL Server SMTP Mail',
@type = N'text/plain',
@message = N'Goodbye MAPI, goodbye Outlook',
@timeout = 10000,
@server = N'mail.sqldev.net'
select RC = @rc
go


Error Dispalyed:-
Error: sending message
Server response: 451 Local Error
put; end with .

give me the proper solution

(Note :- What is this MAPI and by where it is references in sql
server )

Re: how can i send mail by using sql server

sachin shah (sachin28880@gmail.com) writes:
> as per u r guide line i insatalled that xp_smtp_sendmail store
> procedure and assign the grant permission to that SP but after
> executing the following query its showing following error....
>
> declare @rc int
> exec @rc = master.dbo.xp_smtp_sendmail
> @FROM = N'MyEmail@MyDomain.com',
> @FROM_NAME = N'My Full Name',
> @replyto = N'sashah@systime.net',
> @TO = N'sachin28880@gmail.com',
> @priority = N'NORMAL',
> @subject = N'Hello SQL Server SMTP Mail',
> @type = N'text/plain',
> @message = N'Goodbye MAPI, goodbye Outlook',
> @timeout = 10000,
> @server = N'mail.sqldev.net'
> select RC = @rc
> go
>
>
> Error Dispalyed:-
> Error: sending message
> Server response: 451 Local Error
> put; end with .
>
> give me the proper solution

What is says. That is a message from the SMTP server. SMTP requires
that you terminate your message with a dot on a single line. I have
never user xp_smtp_sendmail, but apparently it does not add this dot
for you.

But you should change the SMTP server to the one that you normally use.
SMTP servers do normally not require authentication; anyone connect.
However, an SMTP server is set up to server a set of IP addresses, and if
neither the sender nor the receiver is in that set, the SMTP server should
refused to transmit the mail.

> (Note :- What is this MAPI and by where it is references in sql
> server )

MAPI is the mail protocol used by Outlook and other Microsoft mail
program, and it does not have much to do with SQL Server as such.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx