Adding Select criteria to a mail subject

[jpiscit1]

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 = ???
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.

Here is an example of the select statement I currently run independent of this function:

Select Precip1Production.Date_Time,
Precip1Production.Chem1,
Precip1Production.Chem2,
Precip1Production.Chem3,
Precip1Production.Chem4,
Precip1Production.Chem5,
Precip1Production.Chem6,
Precip1Production.Alum,
Precip1Production.Phenolic,
Precip1Production.Fiber1,
Precip1Production.Fiber2,
Precip1Production.Latex1,
Precip1Production.Latex2,
Precip1Production.CarbonBlack,
Precip1Production.Darvan,
Precip1Production.Caustic,
Precip1Production.Coagulant,
Precip1Production.Hot_Water,
Precip1Production.Wash_Water,
Precip1Production.ManualAdd1,
Precip1Production.ManualAdd2,
Precip1Production.ManualAdd3,
Precip1Production.HardwoodPulp,
Precip1Production.Polymin,
Precip1Production.SodAlum,
Precip1Production.AMX,
Precip1Production.Kymene,
Precip1Production.Batches,
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

Can anyone show me how to embed this?

Thank a lot in advance!

John

 

[MeanGreen]

Something like this:

declare @msg varchar(500)
declare @subj varchar(500)
declare @rcp varchar(1000)
DECLARE @QRY VARCHAR(1000)

select @QRY = 'select * from northwind.dbo.orders'

select @msg = 'Orders Table'

select @subj = 'Orders'

select @rcp = 'me@email.com'

exec Master.dbo.xp_sendmail @recipients = @rcp, @Query = @qry,@message = @msg, @subject = @subj, @no_output = True

That should work.

Good Luck.

 

[jpiscit1]

It looks like you responded with the "how to" set up the mail query. Your referencing xp_sendmail SP. We can't use that here because we have a Lotus Notes (SMTP) mail system. The Stored procedure I referenced above (sp_send_cdosysmail) does that already.

What I am looking for is how to embed my Select Statement into the existing exec statement so that the same results are obtained in the mail.

 

[bnagel2]

Well, take a look at the script below. It basically turns a SQL result set into one big string. If you can follow what I'm doing below, you should be able to alter it to suit your situation. I've aliased the columns in my query as col1, col2, col3, etc., because you have many columns in your query. If you want to send all of those columns to string, then you'll probably want to loop through the column names instead of building a when. . .then statement for each of them. At least one of your columns is a date, so you will have to use the CONVERT function to make it a string.

You might find that this code is complex enough that you'd rather send your recipients a smaller result set or find another way to accomplish this task (like the attachment you mentioned).

-- CREATE A TEMP TABLE SO WE DON'T HAVE TO USE A CURSOR
-- FILL THE TEMP TABLE
SELECT TASKID AS COL1, PROJECTID AS COL2, TASKS AS COL3
INTO #TEMPTBL
FROM TBLTASKSPLAN
WHERE PROJECTID = 1001

-- AN IDENTITY COLUMN ALLOWS US TO PROCESS ROW BY ROW
ALTER TABLE #TEMPTBL
ADD ID_NUM INT IDENTITY(1,1)

-- DECLARE NECESSARY VARIABLES
DECLARE @ROWCNT INT -- ROWCOUNT OF THE #TEMPTBL TABLE
DECLARE @COUNTER INT -- KEEPS TRACK AS WE LOOP
DECLARE @INNERCOUNTER INT -- KEEPS TRACK OF INNER LOOP OVER THE COLUMNS
DECLARE @ITEMLIST VARCHAR(4000)
SET @ROWCNT = (SELECT COUNT(*) FROM #TEMPTBL)
SET @COUNTER = 1
SET @INNERCOUNTER = 1
WHILE @COUNTER <= @ROWCNT
BEGIN
IF @COUNTER = 1 -- FIRST TIME THROUGH
BEGIN
SET @ITEMLIST = (SELECT CAST(COL1 AS VARCHAR(10)) FROM #TEMPTBL WHERE ID_NUM = 1)
SET @INNERCOUNTER = @INNERCOUNTER + 1
WHILE @INNERCOUNTER <= 3 -- 3 IS THE NUMBER OF COLUMNS WE ARE LOOPING THRU
BEGIN
SET @ITEMLIST = @ITEMLIST + ', ' +
CASE WHEN @INNERCOUNTER = 2 THEN
(SELECT CAST(COL2 AS VARCHAR(10)) FROM #TEMPTBL WHERE ID_NUM = 1)
WHEN @INNERCOUNTER = 3 THEN
(SELECT RTRIM(COL3) FROM #TEMPTBL WHERE ID_NUM = 1)
END
SET @INNERCOUNTER = @INNERCOUNTER + 1
END
END
ELSE
BEGIN
WHILE @INNERCOUNTER <= 3 -- 3 IS THE NUMBER OF COLUMNS WE ARE LOOPING THRU
BEGIN
SET @ITEMLIST = @ITEMLIST +
CASE WHEN @INNERCOUNTER = 1 THEN
(SELECT CAST(COL1 AS VARCHAR(10)) FROM #TEMPTBL WHERE ID_NUM = @COUNTER)
WHEN @INNERCOUNTER = 2 THEN
', ' + (SELECT CAST(COL2 AS VARCHAR(10)) FROM #TEMPTBL WHERE ID_NUM = @COUNTER)
WHEN @INNERCOUNTER = 3 THEN
', ' + (SELECT RTRIM(COL3) FROM #TEMPTBL WHERE ID_NUM = @COUNTER)
END
SET @INNERCOUNTER = @INNERCOUNTER + 1
END
END
SET @COUNTER = (@COUNTER + 1)
SET @ITEMLIST = @ITEMLIST + CHAR(13) -- ADD A RETURN AFTER EACH RECORD
SET @INNERCOUNTER = 1
END
EXEC Master.dbo.spSMP_send_cdosysmail 'someone@somewhere.com', 'someone@somewhere.com', 'TEST', @ITEMLIST