Adding Select criteria to Mail Subject

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)

Adding Select criteria to Mail Subject

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-10-09 : 08:29:46

Hi folks!

I am trying to add select criteria to my SQL mail. Eventually I would like to add an attachment but I will save that discussion for another day, unless someone feels compelled to help me with that. For purposes of this question - What I am trying to do is simply add a select statement in to an already existing mail query. Here's the mail query:

declare @Body varchar(4000)
Select @Body = 'test'
exec sp_send_cdosysmail 'someone@microsoft.com','someone2@microsoft.com','Test of CDOSYS',@Body

With the use of a custom stored procedure (for SMTP mail) this statement works fine. I'd like to add a query from my database but am not sure how to embed it into this existing query.

For example:

declare @Body varchar(4000)
Select @Body = (Select * from Precip1Production)
exec sp_send_cdosysmail 'someone@microsoft.com','someone2@microsoft.com','Test of CDOSYS',@Body

The intent would be to have the results show in the body of the mail.

Can anyone help me with this?

Thank a lot in advance!

John

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-09 : 09:42:09

Check this out ...


use pubs
go

declare @body varchar(4000)
select
    @body = coalesce(@body + char(13), '') +
    au_id + ',' +
    au_fname + ',' +
    au_lname + ',' +
    phone + ',' +
    address + ',' +
    city + ',' +
    state + ',' +
    zip + ',' +
    convert(varchar,contract)
from
    pubs.dbo.authors

select @body

Jay White
{0}

Edited by - Page47 on 10/09/2002 09:43:52

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-10-09 : 10:46:03

Ok cool. I've been able to get the email to work based on your comments. Thanks! However, when I set up my select statement as follows:

use BeaverFallsProduction
go

declare @Body varchar(4000)

Select
@body =
Precip1Production.Chem1,
Precip1Production.Chem2
FROM
Precip1Production
WHERE
(Precip1Production.Recipe = '57343A') AND (Precip1Production.Date_Time BETWEEN '10/6/2002 10:17:35 AM' AND '10/9/2002 10:17:35 AM')
ORDER BY
Precip1Production.Date_Time ASC

Select @Body
exec sp_send_cdosysmail 'beams059@sealinfo.com','jrpiscitelli@sealinfo.com','Test of CDOSYS',@Body

It gives me the following error.

Server: Msg 141, Level 15, State 1, Line 12
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I'm sure I'm missing something in the select statement.

Any ideas?

John

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-09 : 10:54:23

You need to go back and understand my post. Yours is not like mine.

Jay White
{0}

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-10-09 : 11:05:02

I know its not. I apoligize for my lack of knowledge in this area. I am still new with SQL. Here is where I am coming from. I have a query that seems to work fine by itself. As follows:

Select Precip1Production.Date_Time,
Precip1Production.Chem1,
Precip1Production.Chem2,
Precip1Production.Recipe
FROM
Precip1Production
WHERE
(Precip1Production.Recipe = '57343A') AND (Precip1Production.Date_Time BETWEEN '10/6/2002 10:17:35 AM' AND '10/9/2002 10:17:35 AM')
ORDER BY
Precip1Production.Date_Time ASC

Now I'd like to embed it into the email exe and show the results in the "body". When I ran yours it worked fine. Mine however, reports the error. What are you doing to the code?

John

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-09 : 11:22:44

@body is a varchar variable.
Your select statement is a set of rows and column.

In order to put rows and columns into a varchar variable, you need to concatenate all the values in the set into a single string. My query uses a simple '+' operator to concatenate columns together and uses the method outlines in the this article to concatenate those rows together using a char(13) as the delimter.

Jay White
{0}

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-10-09 : 14:45:04

Thanks again for your response Jay. I read the information you provided and am getting a better handle on this. Was able to execute something but it was not what I was looking for. If you (or anyone) could provide me with an example (using some of my fields) I would greatly appreciate it!! Again I am just looking to reproduce the query results only in an email. By the way, everything is numeric but the date column.

Thanks in advance,

John

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-10-10 : 10:31:26

I don't seem to be getting very far with this.... was hoping for a little more help .... I have been able to produce the data (in the email) in the following format:

95.700.00, 95.200.00, 95.900.00, 95.800.00, 96.100.00, 95.800.00, 95.700.00, 96.000.00

Using:

use BeaverFallsProduction
go

declare @Body varchar(4000)
select
@body = coalesce(@body + ', ', '') +
Convert(varchar, Chem1) + Convert(varchar, Chem2)
FROM
Precip1Production
WHERE
(Precip1Production.Recipe = '57343A') AND (Precip1Production.Date_Time BETWEEN '10/6/2002 10:17:35 AM' AND '10/9/2002 10:17:35 AM')
ORDER BY
Precip1Production.Date_Time ASC

Select @Body
exec sp_send_cdosysmail 'someone@microsoft.com','someone2@microsoft.com','Test of CDOSYS',@Body

What I am looking for is:

Chem1 Chem2
95.70 .00
95.20 .00
95.90 .00
95.80 .00
96.10 .00
95.80 .00
95.70 .00

Which can be obtained by:

SELECT
Precip1Production.Chem1,
Precip1Production.Chem2
FROM
Precip1Production
WHERE
(Precip1Production.Recipe = '57343A') AND (Precip1Production.Date_Time BETWEEN '10/6/2002 8:07:34 AM' AND '10/9/2002 8:07:34 AM')
ORDER BY
Precip1Production.Date_Time ASC

??

John

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-10 : 10:54:21

select
@body = coalesce(@body + char(13), '') +
Convert(varchar, Chem1) + ' ' + Convert(varchar, Chem2)
FROM
Precip1Production
WHERE
(Precip1Production.Recipe = '57343A') AND (Precip1Production.Date_Time BETWEEN '10/6/2002 10:17:35 AM' AND '10/9/2002 10:17:35 AM')
ORDER BY
Precip1Production.Date_Time ASC

Jay White
{0}

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-10-10 : 11:07:52

Jay,

Your a life saver. Thanks a million. That works excellent!

John

schinni
Yak Posting Veteran

66 Posts

Posted - 2003-03-10 : 11:21:13

Can we add delete/update queries as we are doing select query
in then body of that mail

I am planning to use this almost close to xp_Sendmail.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources