adding query results to email body using CDOSYSmail

Thanks in advance - Steve.
I'm running SQL 7.0 with Exchange 2003 SP2 (SP.?) which uses SSL to preclude
using SQL Mail (as far as I know). So I've been working on using CDOSYSmail
to send email, and attachment, and the results from a stored procedure (a
set of queries that generates 6 summary reports with varying columns). I've
inserted the results from the stored procedure into a table called, results
varchar(4000).
I'm trying to insert the results from 'sproc_name' into the body of an email
and could use some help.
The stored procedure, 'usp_send_cdosysmail' is working fine except I can't
get the results from another stored procedure into the body of an email. The
table 'result' is one column varchar(4000); I'd prefer just executing the
'sproc_name' and avoid the cursor since i'm new to cursors. I receive the
error below:
Server: Msg 170, Level 15, State 1, Line 17
Line 17: incorrect syntax near @.body1
declare @.Body1 varchar(4000)
set @.Body1 = ''
declare r_cursor cursor for
select result from test.dbo.result
open r_cursor
declare @.r_return cursor
exec db_name.dbo.sproc_name @.r_return = OUTPUT
while (@.@.fetch_status=0)
begin
fetch next from @.r_return
end
close r_cursor
@.body1 = @.r_return
deallocate r_cursor
exec master..usp_send_cdosysmail
@.from='user1@.domain.com',
@.to ='user2@.domain.com',
@.subject ='Test',
@.body = @.body1,
@.attachments = 'C:\test.txt',
@.smtpserver = 'mailserver.domain.com',
@.bodytype ='TEXTBody' -- 'HTMLBody'Hi Steve
Assuming your result table is declared as something like:
CREATE TABLE dbo.result ( id int not null identity, result varchar(400))
Then each row returned from your stored procedure will be a separate row in
dbo.result. You can then cursor through the table and get each row.
INSERT INTO dbo.result (result)
EXEC dbo.sproc_name
DECLARE @.Body1 varchar(4000)
DECLARE @.lineresult varchar(400)
SET @.Body1 = ''
DECLARE r_cursor CURSOR FOR
SELECT result FROM dbo.result ORDER BY id
OPEN r_cursor
FETCH NEXT FROM r_cursor INTO @.Body1
WHILE @.@.FETCH_STATUS=0
BEGIN
FETCH NEXT FROM r_cursor INTO @.lineresult
SET @.Body1 = @.Body1 + CHAR(13) + CHAR(10) + @.lineresult
END
CLOSE r_cursor
DEALLOCATE r_cursor
John
"Steve" wrote:

> Thanks in advance - Steve.
> I'm running SQL 7.0 with Exchange 2003 SP2 (SP.?) which uses SSL to preclu
de
> using SQL Mail (as far as I know). So I've been working on using CDOSYSma
il
> to send email, and attachment, and the results from a stored procedure (a
> set of queries that generates 6 summary reports with varying columns). I'v
e
> inserted the results from the stored procedure into a table called, result
s
> varchar(4000).
> I'm trying to insert the results from 'sproc_name' into the body of an ema
il
> and could use some help.
> The stored procedure, 'usp_send_cdosysmail' is working fine except I can't
> get the results from another stored procedure into the body of an email. T
he
> table 'result' is one column varchar(4000); I'd prefer just executing the
> 'sproc_name' and avoid the cursor since i'm new to cursors. I receive the
> error below:
> Server: Msg 170, Level 15, State 1, Line 17
> Line 17: incorrect syntax near @.body1
> declare @.Body1 varchar(4000)
> set @.Body1 = ''
> declare r_cursor cursor for
> select result from test.dbo.result
> open r_cursor
> declare @.r_return cursor
> exec db_name.dbo.sproc_name @.r_return = OUTPUT
>
> while (@.@.fetch_status=0)
> begin
> fetch next from @.r_return
> end
> close r_cursor
> @.body1 = @.r_return
> deallocate r_cursor
> exec master..usp_send_cdosysmail
> @.from='user1@.domain.com',
> @.to ='user2@.domain.com',
> @.subject ='Test',
> @.body = @.body1,
> @.attachments = 'C:\test.txt',
> @.smtpserver = 'mailserver.domain.com',
> @.bodytype ='TEXTBody' -- 'HTMLBody'
>
>|||Hi John - big thanks!
I did not have an id property, only a column entitled results with datatype
varchar(4000).
Cursors need an id column (i'm familiar w/ the identity property - very
handy).
For my education, wow do the char(13) an char(10) work inconjunction with
@.body1 and @.lineresult to concatenate all the rows together? I interpret
@.body1 as being all the previous rows and @.lineresult being the current row,
but don't understand how char(13) and char(10) impact the query?
Thanks!
Steve
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:81EFD80A-10CE-4308-B710-7ABB7E79FD53@.microsoft.com...
> Hi Steve
> Assuming your result table is declared as something like:
> CREATE TABLE dbo.result ( id int not null identity, result varchar(400))
> Then each row returned from your stored procedure will be a separate row
in[vbcol=seagreen]
> dbo.result. You can then cursor through the table and get each row.
> INSERT INTO dbo.result (result)
> EXEC dbo.sproc_name
> DECLARE @.Body1 varchar(4000)
> DECLARE @.lineresult varchar(400)
> SET @.Body1 = ''
> DECLARE r_cursor CURSOR FOR
> SELECT result FROM dbo.result ORDER BY id
> OPEN r_cursor
> FETCH NEXT FROM r_cursor INTO @.Body1
> WHILE @.@.FETCH_STATUS=0
> BEGIN
> FETCH NEXT FROM r_cursor INTO @.lineresult
> SET @.Body1 = @.Body1 + CHAR(13) + CHAR(10) + @.lineresult
> END
> CLOSE r_cursor
> DEALLOCATE r_cursor
> John
> "Steve" wrote:
>
preclude[vbcol=seagreen]
CDOSYSmail[vbcol=seagreen]
(a[vbcol=seagreen]
I've[vbcol=seagreen]
results[vbcol=seagreen]
email[vbcol=seagreen]
can't[vbcol=seagreen]
The[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]|||Hi
The id column will be necessary if you want to keep the order the same as
the order returned by the stored procedure. The CHAR(13) and CHAR(10) are to
add a carriage return and linefeed into your results, they have no impact on
the query as they are not part of it. If you don't need them then they can b
e
omitted, but then the columns may not align.
@.Body1 was the variable you used for the email body, if you return more than
one line then the cursor will loop through subsequent lines by returning the
m
in @.lineresult and then appending them to @.Body1. The first line returned ca
n
go straight into @.Body1.
I am not sure how big the body of your text could be, but if it is 4000
characters it may not be large enough for your needs. You may want to look a
t
creating a DTS package to create the files and (say) using XPSMTP to send it
http://www.sqldev.net/xp/xpsmtp.htm. It may be better to put the results int
o
a spreadsheet rather than leaving them as text, DTS can do this. See books
online for more information on DTS and check out
http://www.sqldts.com/default.aspx
John
"Steve" wrote:

> Hi John - big thanks!
> I did not have an id property, only a column entitled results with datatyp
e
> varchar(4000).
> Cursors need an id column (i'm familiar w/ the identity property - very
> handy).
> For my education, wow do the char(13) an char(10) work inconjunction with
> @.body1 and @.lineresult to concatenate all the rows together? I interpret
> @.body1 as being all the previous rows and @.lineresult being the current ro
w,
> but don't understand how char(13) and char(10) impact the query?
> Thanks!
> Steve
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:81EFD80A-10CE-4308-B710-7ABB7E79FD53@.microsoft.com...
> in
> preclude
> CDOSYSmail
> (a
> I've
> results
> email
> can't
> The
> the
> the
>
>