This is driving me crazy! The SQL Statement refenced is shown at the end of this email.
When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?
SQL View......
SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,
Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR
(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total
FROM dbo.QRY_FacNew_Term
WHERE (Exclude <> 'Y')
ORDER BY Category, Client_NameUse brackets if name includes space:
[drawn_GT5MIL_LE365Days ] = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Showing posts with label email. Show all posts
Showing posts with label email. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
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
>
>
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
> can't
> The
> the
> the
>
>
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 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 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
> 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
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
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 be
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 them
in @.lineresult and then appending them to @.Body1. The first line returned can
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 at
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 into
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 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
> > 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
> 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'
> > >
> > >
> > >
>
>
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 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 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
> 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
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
> > 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
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 be
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 them
in @.lineresult and then appending them to @.Body1. The first line returned can
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 at
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 into
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 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
> > 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
> 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
> > > 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'
> > >
> > >
> > >
>
>
Thursday, March 8, 2012
adding operator to sql server 2000
I need to add an operator to sql server 2000, but for some reason, the email
isn't getting sent out to them.
I have them configured the same as me, and have sent them email several
times, but they haven't received it. I even added my email address to the
"name" in there, and I haven't gotten email addressed to them, but my email
as another operator seems to work fine.
Is there a log of email that is accumulated on sql server's logfiles or
something that you can tell if it's left the server yet?
Any help appreciated.
Thanks,
SCtry xp_sendmail in Query Analyzer. It will show errors on failure.
"Goober at christianDOTnet" <me@.privacy.net> wrote in message
news:OSoim4weEHA.3348@.TK2MSFTNGP12.phx.gbl...
> I need to add an operator to sql server 2000, but for some reason, the
email
> isn't getting sent out to them.
> I have them configured the same as me, and have sent them email several
> times, but they haven't received it. I even added my email address to the
> "name" in there, and I haven't gotten email addressed to them, but my
email
> as another operator seems to work fine.
> Is there a log of email that is accumulated on sql server's logfiles or
> something that you can tell if it's left the server yet?
> Any help appreciated.
> Thanks,
> SC
>
>|||This seems to work fine.
However, when I go to the Operators menu on the tree for the server, and put
in an email address to send it to & then click test, it says it sends it.
However, neither of us are receiving it.
On another operator tab, it'll do the same thing. It had been sending me an
email when a job completed.
SC
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:e7K$LVxeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> try xp_sendmail in Query Analyzer. It will show errors on failure.
>
> "Goober at christianDOTnet" <me@.privacy.net> wrote in message
> news:OSoim4weEHA.3348@.TK2MSFTNGP12.phx.gbl...
> > I need to add an operator to sql server 2000, but for some reason, the
> email
> > isn't getting sent out to them.
> >
> > I have them configured the same as me, and have sent them email several
> > times, but they haven't received it. I even added my email address to
the
> > "name" in there, and I haven't gotten email addressed to them, but my
> email
> > as another operator seems to work fine.
> >
> > Is there a log of email that is accumulated on sql server's logfiles or
> > something that you can tell if it's left the server yet?
> >
> > Any help appreciated.
> >
> > Thanks,
> >
> > SC
> >
> >
> >
>|||If it is working for some operators, but not others.
1. Try sending an email to the failed email address directly ( outside SQL )
to make sure the email is working.
2. drop the operator and re-add the operator in SQL Agent, Be sure to use
the entire email address.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Goober at christianDOTnet" <me@.privacy.net> wrote in message
news:OSoim4weEHA.3348@.TK2MSFTNGP12.phx.gbl...
> I need to add an operator to sql server 2000, but for some reason, the
email
> isn't getting sent out to them.
> I have them configured the same as me, and have sent them email several
> times, but they haven't received it. I even added my email address to the
> "name" in there, and I haven't gotten email addressed to them, but my
email
> as another operator seems to work fine.
> Is there a log of email that is accumulated on sql server's logfiles or
> something that you can tell if it's left the server yet?
> Any help appreciated.
> Thanks,
> SC
>
>
isn't getting sent out to them.
I have them configured the same as me, and have sent them email several
times, but they haven't received it. I even added my email address to the
"name" in there, and I haven't gotten email addressed to them, but my email
as another operator seems to work fine.
Is there a log of email that is accumulated on sql server's logfiles or
something that you can tell if it's left the server yet?
Any help appreciated.
Thanks,
SCtry xp_sendmail in Query Analyzer. It will show errors on failure.
"Goober at christianDOTnet" <me@.privacy.net> wrote in message
news:OSoim4weEHA.3348@.TK2MSFTNGP12.phx.gbl...
> I need to add an operator to sql server 2000, but for some reason, the
> isn't getting sent out to them.
> I have them configured the same as me, and have sent them email several
> times, but they haven't received it. I even added my email address to the
> "name" in there, and I haven't gotten email addressed to them, but my
> as another operator seems to work fine.
> Is there a log of email that is accumulated on sql server's logfiles or
> something that you can tell if it's left the server yet?
> Any help appreciated.
> Thanks,
> SC
>
>|||This seems to work fine.
However, when I go to the Operators menu on the tree for the server, and put
in an email address to send it to & then click test, it says it sends it.
However, neither of us are receiving it.
On another operator tab, it'll do the same thing. It had been sending me an
email when a job completed.
SC
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:e7K$LVxeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> try xp_sendmail in Query Analyzer. It will show errors on failure.
>
> "Goober at christianDOTnet" <me@.privacy.net> wrote in message
> news:OSoim4weEHA.3348@.TK2MSFTNGP12.phx.gbl...
> > I need to add an operator to sql server 2000, but for some reason, the
> > isn't getting sent out to them.
> >
> > I have them configured the same as me, and have sent them email several
> > times, but they haven't received it. I even added my email address to
the
> > "name" in there, and I haven't gotten email addressed to them, but my
> > as another operator seems to work fine.
> >
> > Is there a log of email that is accumulated on sql server's logfiles or
> > something that you can tell if it's left the server yet?
> >
> > Any help appreciated.
> >
> > Thanks,
> >
> > SC
> >
> >
> >
>|||If it is working for some operators, but not others.
1. Try sending an email to the failed email address directly ( outside SQL )
to make sure the email is working.
2. drop the operator and re-add the operator in SQL Agent, Be sure to use
the entire email address.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Goober at christianDOTnet" <me@.privacy.net> wrote in message
news:OSoim4weEHA.3348@.TK2MSFTNGP12.phx.gbl...
> I need to add an operator to sql server 2000, but for some reason, the
> isn't getting sent out to them.
> I have them configured the same as me, and have sent them email several
> times, but they haven't received it. I even added my email address to the
> "name" in there, and I haven't gotten email addressed to them, but my
> as another operator seems to work fine.
> Is there a log of email that is accumulated on sql server's logfiles or
> something that you can tell if it's left the server yet?
> Any help appreciated.
> Thanks,
> SC
>
>
Friday, February 24, 2012
Adding Group Subscriptions
Hi,
Can anyone let me know how to add a group in the domain to reporting
services.I tried adding email address and could easily subscribe to reports.
but when i tried to add a group by spacifying domainname\group name, it did
not work. Can anyone please help!!!
SachinI got a solution for this. All i did was to add a group email in our email
server. Reporting services lets us enter only email address .
"Sachin" wrote:
> Hi,
> Can anyone let me know how to add a group in the domain to reporting
> services.I tried adding email address and could easily subscribe to reports.
> but when i tried to add a group by spacifying domainname\group name, it did
> not work. Can anyone please help!!!
> Sachin
Can anyone let me know how to add a group in the domain to reporting
services.I tried adding email address and could easily subscribe to reports.
but when i tried to add a group by spacifying domainname\group name, it did
not work. Can anyone please help!!!
SachinI got a solution for this. All i did was to add a group email in our email
server. Reporting services lets us enter only email address .
"Sachin" wrote:
> Hi,
> Can anyone let me know how to add a group in the domain to reporting
> services.I tried adding email address and could easily subscribe to reports.
> but when i tried to add a group by spacifying domainname\group name, it did
> not work. Can anyone please help!!!
> Sachin
Thursday, February 16, 2012
Adding current date into attachment filename in reporting service
Hi there,
I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.
My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.
My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.
Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005
Regards,
Hello...
I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?
Thanks.
You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1
Hope this helps.
Jarret
Adding current date into attachment filename in reporting service
Hi there,
I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.
My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.
My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.
Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005
Regards,
Hello...
I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?
Thanks.
You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1
Hope this helps.
Jarret
Subscribe to:
Posts (Atom)