E-mail query results - SQL Server Forums

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)

E-mail query results

Author

Topic

garmon648
Starting Member

7 Posts

Posted - 2010-07-08 : 15:06:59

I am trying to send query results using CDOSYS mail. Here is what I have so far:

Declare @drugID varchar(4000),@lineresult varchar(4000),
@SEND VARCHAR(256), @RECP VARCHAR(256), @TITLE VARCHAR(256), @BODY VARCHAR(4000), @SERVERNAME VARCHAR(256)
set @drugID = ''
declare r_cursor CURSOR FOR
select convert(char(20),a.drug_dose_id)+ ' ' +a.Drug_display_name+ ' ' +convert(char(20),c.qty_received)+ ' ' +c.restocked_by
from ahi_drug_dose a, ahi_carousel_bin b,ahi_reorder_log_item_container c
where a.drug_dose_id = b.item_id and a.drug_dose_id = c.item_id and b.drug_attribute_id = 2 and c.qty_received = 1

open r_cursor
fetch NEXT FROM r_cursor INTO @drugID
while @@FETCH_STATUS = 0
Begin
FETCH NEXT FROM r_cursor INTO @lineresult
SET @drugID = @lineresult
END
CLOSE r_cursor
DEALLOCATE r_cursor
SET @SEND='System@regional.com'
SET @RECP='email@email.org'
SET @TITLE='Bulk Items Received as 1'
SET @BODY = @lineresult
SET @SERVERNAME= 'TESTCRXSERV'
exec sp_send_cdosysmail @SEND,@RECP,@TITLE,@BODY,@SERVERNAME

I am only getting the last row in the e-mail. What am I doing wrong??

thanks

garmon648
Starting Member

7 Posts

Posted - 2010-07-09 : 13:17:10

Ok, got this to work except it repeats the last row of the query for some reason.

Declare @drugID varchar(4000),@lineresult varchar(4000),
@SEND VARCHAR(256), @RECP VARCHAR(256), @TITLE VARCHAR(256), @BODY VARCHAR(4000), @SERVERNAME VARCHAR(256)
set @drugID = ''
declare r_cursor INSENSITIVE CURSOR FOR
select convert(char(7),a.drug_dose_id)+ ' ' +a.Drug_display_name+ ' ' +convert(char(4),c.qty_received)+ ' ' +c.restocked_by+ ' '
from ahi_drug_dose a, ahi_carousel_bin b,ahi_reorder_log_item_container c
where a.drug_dose_id = b.item_id and a.drug_dose_id = c.item_id and b.drug_attribute_id = 2 and c.qty_received = 1
open r_cursor
fetch next from r_cursor INTO @drugID
while @@FETCH_STATUS = 0
Begin
FETCH next from r_cursor INTO @lineresult
set @drugID = @drugID + CHAR(13) + CHAR(10) + @lineresult
END
CLOSE r_cursor
DEALLOCATE r_cursor
SET @SEND='System@regional.com'
SET @RECP='email@email.org'
SET @TITLE='Bulk Items Received as 1'
SET @BODY = @drugID
SET @SERVERNAME= 'TESTCRXSERV'
exec sp_send_cdosysmail @SEND,@RECP,@TITLE,@BODY,@SERVERNAME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources