Prev: ActiveX component can't create object (DSO)
Next: [TCP/IP Sockets]ConnectionCheckForData (CheckforData())
From: Ant on 5 Jan 2007 17:41
Thanks for the information Tracy, I'll take on board the idea of writing to a
staging table.

As for xp_smtp_sendmail & BLAT, I'm using SQL2000. is this available to me
in SQL2000 ?
If not, how do I use xp_sendmail without receiving the error I mentioned?

"Syntax error or access violation"


Thanks very much for your answer.

Ant

"Tracy McKibben" wrote:

> Ant wrote:
> > Hi,
> >
> > I need to send an email on an trigger on Insert.
> >
> > Is xp_sendmail what I use to do this with? I tried using it however
> > received an error message: "Syntax error or access violation"
> >
> > This is basically what I am executing:
> >
> > xp_startmail
> >
> > xp_sendmail {@recipients = 'receiver(a)someServer.com'},
> > @message = 'hello world',@subject = 'testmail'
> >
> > I think the syntax is ok, so what kind of access violation would be
> > occuring? I am sending it from a connection with admin rights. This is being
> > sent to a web based email account.
> >
> > Any help or advise would be appreciated
> >
> > Thank you
> > Ant
> >
> >
>
> Firstly, you don't want to do this within a trigger. Sending mail is an
> expensive operation, and is a huge potential point of failure. Having a
> mail problem within a trigger can cause a hung transaction, major
> blocking problems, lots of nasty stuff. The "accepted" method of doing
> what you're attempting is to use the trigger to write to a staging
> table. Another seperate process monitors that staging table, looking
> for new actions to take, that process will send the email.
>
> As far as sending the message - have you configured SQL Mail? You'll
> need to do that before you can use xp_sendmail. If you haven't already
> done so, don't. There are better ways of sending mail from SQL. Read
> up on:
>
> - xp_smtp_sendmail - a replacement for xp_sendmail that uses a standard
> SMTP server
>
> - BLAT - a command-line mailer that you can call through xp_cmdshell
>
> Both work well, I've used them both.
>
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
From: John Bell on 6 Jan 2007 05:35
Hi Sean

I looked at the parameter list for the stored procedure in the object
explorer and nothing was there for @ansi_attachment, and it would be if there
was a parameter with that name.

I suggest you add your vote to the list. I am not sure what you are doing
with this file, but you may want to consider trying making this file a
different format such as excel.

John

"Sean Gahan" wrote:

> John,
>
> Thanks for the info and links, I will check them out. By the way, SQL7
> could do the ANSI attachments and when MS released SQL2000 they dropped
> supporting it. I know my company complained (I'm sure others did too) and
> the issue was addressed as a Quick Fix Engineering (QFE) and then later
> included in one of the service packs. If you want to try it, this is the
> syntax:
>
>
>
> exec master.dbo.xp_sendmail @recipients='someone(a)somewhere.com',@subject='test',@message='body',@no_output='false',
>
> @attachments='someFile.txt',@no_header='true',@width=120,@dbuse='some_db',@attach_results='true',
>
> @query='select fname from users', @ansi_attachment='True'
>
>
>
>
>
> Try opening up the attached document in with WordPad, and try running
> msdb.dbo.sp_send_dbmail on SQL 2005 and opening the attachment in WordPad
> and you will see the issue.
>
>
>
> Best regards,
>
> Sean Gahan
>
>
>
>
>
>
>
>
> "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message
> news:742E25A0-483D-4374-85CC-0C1EAD2301B7(a)microsoft.com...
> > Hi Sean
> >
> > I have never seen or used a @ansi_attachment parameter for xp_sendmail and
> > it does not seem to be documented in BOL, but
> > http://support.microsoft.com/kb/280720/en-us does talk of a registry
> > setting,
> > but I don't think there is an equivalent for sp_send_dbmail in SQL 2005 as
> > this has been logged at
> > https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127040
> > and you may wish to vote for the issue. Checking December's CTP for SP2
> > does
> > not show a parameter with this name, so I am not sure what the comment
> > about
> > it being fixed is referring to.
> >
> > John
> >
> > "Sean Gahan" wrote:
> >
> >> Jon,
> >>
> >> Sorry for being a bit vague, what I am referring to is the parameter
> >> @ATTACH_QUERY_RESULT_AS_FILE='true' for the sp_send_dbmail on SQL2005 and
> >> @attach_results='true'/ @ansi_attachment='True' for xp_sendmail on
> >> SQL2000.
> >> It appears that there is no way to send an ANSI attachment for SQL2005,
> >> everything is sent in UNICODE. UNICODE presents a bit of an issue when
> >> the
> >> systems in place are expecting ANSI. Any advice is greatly appreciated.
> >>
> >>
> >>
> >> Best regards,
> >>
> >>
> >>
> >> Sean Gahan
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message
> >> news:9675416B-9113-418B-B8A5-C3B889679EC4(a)microsoft.com...
> >> > Hi Sean
> >> >
> >> > I am not sure what you mean by default attachment type, any file can be
> >> > attached be it excel, text or any other type! Are you talking about the
> >> > attachedfile parameter with is not unicode?
> >> >
> >> > John
> >> >
> >> > "Sean Gahan" wrote:
> >> >
> >> >> With SQL2000 xp_sendmail you could include attachments and specify
> >> >> that
> >> >> they
> >> >> were ANSI. SQL2005 uses sp_send_dbmail and the default attachment is
> >> >> UNICODE, is there a way to specify ANSI instead.
> >> >>
> >> >>
> >> >>
> >> >> Best Regards,
> >> >>
> >> >>
> >> >>
> >> >> Sean Gahan
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
From: Tracy McKibben on 8 Jan 2007 09:43
Ant wrote:
> Thanks for the information Tracy, I'll take on board the idea of writing to a
> staging table.
>
> As for xp_smtp_sendmail & BLAT, I'm using SQL2000. is this available to me
> in SQL2000 ?

Yes, both can be used with SQL 2000.


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
From: Peter Apostolakopoulos on 11 Jan 2007 05:53
Assuming the following:

1. You are logged on the with the Windows account used to run the SQLServer
service.
2. The Windows account used to run the SQLServer service has full mailbox
permissions to the mailbox used.
3. The Windows account used to run the SQLServer service has the Send As
permission for the mailbox used.

then the only thing that I found that will work is stopping and re-starting
the SQLServer service, not SQLAgent.

This problem is also driving me mad and I am having intermittent problems
where I know the setup is correct but xp_sendmail just stops working
correctly.

Hope it helps.

"Vijay" wrote:

>
> Hai forum,
>
> When i executed the the
> the following query :
> exec xp_sendmail 'vijay(a)bluechipsw.com','Hai vijay'
>
> it gave the following error message:-
>
> xp_sendmail: failed with mail error 0x8004011d
>
> then i executed
>
> exec xp_stopmail --Stopped mail session
>
> afterwards i executed
>
> exec xp_startmail
>
> once again i gave the same error message:-
>
> xp_startmail: failed with mail error 0x8004011d
>
> Please help me immediately
>
>
> --
> Vijay
> ------------------------------------------------------------------------
> Vijay's Profile: http://www.dbtalk.net/m594
> View this thread: http://www.dbtalk.net/t322563
>
>
From: John Bell on 11 Jan 2007 06:26
Hi Peter

"Peter Apostolakopoulos" wrote:

> Assuming the following:
>
> 1. You are logged on the with the Windows account used to run the SQLServer
> service.
> 2. The Windows account used to run the SQLServer service has full mailbox
> permissions to the mailbox used.
> 3. The Windows account used to run the SQLServer service has the Send As
> permission for the mailbox used.
>
> then the only thing that I found that will work is stopping and re-starting
> the SQLServer service, not SQLAgent.
>
> This problem is also driving me mad and I am having intermittent problems
> where I know the setup is correct but xp_sendmail just stops working
> correctly.
>
> Hope it helps.
>
I seem to remember that there was some problem where SQL Mail will stop if
it can't connect to the exchange server, but I can't remember the KB article
number or find it. Have you considered using SMTP instead
http://www.sqldev.net/xp/xpsmtp.htm ?

John

First  |  Prev  | 
Pages: 1 2
Prev: ActiveX component can't create object (DSO)
Next: [TCP/IP Sockets]ConnectionCheckForData (CheckforData())