Prev: ActiveX component can't create object (DSO)
Next: [TCP/IP Sockets]ConnectionCheckForData (CheckforData())
From: Ant on 5 Jan 2007 02:51
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


From: Tracy McKibben on 5 Jan 2007 08:19
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: Sean Gahan on 5 Jan 2007 14:16
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: John Bell on 5 Jan 2007 16:15
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: Sean Gahan on 5 Jan 2007 17:19
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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>>
>>
>>


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