Controling xp_sendmail

Hi,
I'd like the email be sent only when the query has results. When it doesn't
(null or 0), the email should not be sent:
EXEC master..xp_sendmail @.recipients = 'mike',
@.message = 'Message text',
@.query = '--',
@.subject = 'SQL Mail test to attach query results',
@.dbuse = 'pubs'
Howto?
TIA
MikeMike,
In this situation, you'll have to run the query twice. Once to see if you
get results, and then once in xp_sendmail.
Of course, you could pump the results from the test into a different table,
and then query that from xp_sendmail. But I'd only consider doing this if
your query really can't be run twice.
Rob
"Mike_B" wrote:

> Hi,
> I'd like the email be sent only when the query has results. When it doesn'
t
> (null or 0), the email should not be sent:
>
> EXEC master..xp_sendmail @.recipients = 'mike',
> @.message = 'Message text',
> @.query = '--',
> @.subject = 'SQL Mail test to attach query results',
> @.dbuse = 'pubs'
>
> Howto?
> TIA
> Mike
>
>

Continue after error

I have an SSIS package that uses a for each loop to send an order confirmation e-mail. If it does not find an email I need the package to continue after the failure path. The package stops after the failure, but I need it to continue with the next iteration of the for each loop. How can I do this?

Suppress execution error propagation up the container hierarchy, so that the foreach loop container is unaware of errors in its own executables.

Specifically, set the Propagate system variable to false on an event handler scoped below the foreach loop container. Propagate is a system variable which controls upward execution error propagation.

See the following post for more details on how to set the value of this system variable.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1561849&SiteID=1

Now, for validation errors, which will always bubble up at least one level up the container hierarchy, you'll have to set do something like setting the MaximumErrorCount to a large number on the foreach loop container.

|||

That works for me. Thanks.

constructing strings from table

Hi friends,
please help me in selecting values from the table
the record is as follows:
Id HomePhone WorkPhone Mobile Email
20 2323223 323232232 test@.test.com
i have to select values as follows.
Id DeviceType DeviceInfo
20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
the one solution is:
select
'HomePhone, Mobile, Email' AS DeviceType
HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
DeviceInfo
from table where Id = 20
but here the work phone number is not available so that information
has to be truncated...
Thanks in Advance
Arunkumar.DUse the same thinking as was suggested in my earlier reply:
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NOT NULL THEN 'WorkPhone' + ', ' ELSE '' END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM
(
SELECT 20 AS No, '23232' AS HomePhone, NULL AS WorkPhone, '98327' AS Mobile
) AS x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1179848438.242226.4580@.n15g2000prd.googlegroups.com...
> Hi friends,
> please help me in selecting values from the table
> the record is as follows:
> Id HomePhone WorkPhone Mobile Email
> 20 2323223 323232232 test@.test.com
> i have to select values as follows.
> Id DeviceType DeviceInfo
> 20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
>
> the one solution is:
> select
> 'HomePhone, Mobile, Email' AS DeviceType
> HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
> DeviceInfo
> from table where Id = 20
> but here the work phone number is not available so that information
> has to be truncated...
> Thanks in Advance
> Arunkumar.D
>

Constructing Email message

Hi,

I am constructing a Message (Body) for sending our Emails. It is around
3000 characters long. But for whatever reason, the last line seems to
be broken with a "!" exclamatory mark in it, which results in
displaying the constructed image path as a broken one.

How to resolve this ?. Thanks.

Regards,
KarthickNo idea - what version of SQL Server, how are you building the message
body, which data type are you using for the message body, how are you
going to send the mail, what does "image path" refer to, can you post a
simplified SQL script to show the problem etc.

As a complete guess, you've declared the message body as varchar but
the path has Unicode characters in it, so you need to use nvarchar. But
without more information, that's probably wrong.

Simon|||Simon, thanks for your reply.

I am using SQL Server 2000. The Message body is NTEXT datatype in the
database. And I am constructing the message body like the following:

SELECT @.MessageBody = '<!DOCTYPE HTML PUBLIC "-//IETF//DTD
HTML//EN">'
SELECT @.MessageBody = @.MessageBody + '<html><head><meta
http-equiv="Content-Type" '
SELECT @.MessageBody = @.MessageBody + 'content="text/html;charset=' +
@.CharSet + '">'
SELECT @.MessageBody = @.MessageBody + '<title>Title goes here:
Ticket</title>'
SELECT @.MessageBody = @.MessageBody + '<link rel="stylesheet"
type="text/css" '

-- I have 67 lines of the @.MessageBody construction and at the end of
the stored procedure I am doing a EXEC to insert this @.MessageBody onto
another table and our third party vendor picks up the Emails from the
table and send them out. So I don't send out the emails manually, all I
do is insert the email contents onto a table and the rest is taken care
of.

Please help. Thanks.

Karthick|||It's still a little unclear (at least to me) - @.MessageBody can't be
ntext, because you can't declare an ntext variable. And you don't say
where the invalid data appears - if you SELECT @.MessageBody before
INSERTing it, is the data correct? Or is it only wrong after INSERTing?
And what does "doing an EXEC" mean? Are you using dynamic SQL, or a
stored procedure to do the INSERT?

Rather than describing your problem, I suggest that you try to produce
a (simplified) script to illustrate your problem - code is always
clearer than a description, and if other people can quickly copy and
paste into Query Analyzer, you're more likely to get a useful answer.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon