data export to CSV

I have a report that produces over 3 million rows, this report is
export to CSV and is scheduled to run weekly.
Sometimes the report is needed to run manually. Initially this report
timed out, now the report runs but takes a very long time to complete.
If the stored procedure is run directly it takes about 5 mins to
execute. Running in reporting services takes upward of an hour.
Does anyone have suggestions on how to speed up this report.
my initial thoughts would be to use SSRS to populate the parameters
and maybe hook into the reporting engine and execute the stored
procedure directly with the parameters defined
thanks in advance
IanHi, how run this report? http? web service? how?
--
----
Microsoft M.V.P en SQLServer
SQLTotal Consulting - Servicios en SQLServer
Email: maxi.da@.gmail.com.nospam
----
<emery.ian@.googlemail.com> escribió en el mensaje
news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>I have a report that produces over 3 million rows, this report is
> export to CSV and is scheduled to run weekly.
> Sometimes the report is needed to run manually. Initially this report
> timed out, now the report runs but takes a very long time to complete.
> If the stored procedure is run directly it takes about 5 mins to
> execute. Running in reporting services takes upward of an hour.
> Does anyone have suggestions on how to speed up this report.
> my initial thoughts would be to use SSRS to populate the parameters
> and maybe hook into the reporting engine and execute the stored
> procedure directly with the parameters defined
>
> thanks in advance
> Ian|||Do not waste your time trying to run the report different ways, it will not
affect the time.
Your time is coming in two places. One, does it really only take 5 minutes
to extract 5 million records? I'm not sure how it works when executing a
stored procedure from Query Analyzer that returns 5 million records, whether
it really retrieves all the records or not. Anyway, I think that is one
area, the number of records being physically returned takes time.
Second, RS 2000 and RS 2005 does all rendering in RAM. It does not page
anything out to disk or utilize the disk at all when rendering. I am pretty
sure this is changing substantially in RS 2008. Adding more RAM to the
server will help.
The other option is to use SSIS instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<emery.ian@.googlemail.com> wrote in message
news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>I have a report that produces over 3 million rows, this report is
> export to CSV and is scheduled to run weekly.
> Sometimes the report is needed to run manually. Initially this report
> timed out, now the report runs but takes a very long time to complete.
> If the stored procedure is run directly it takes about 5 mins to
> execute. Running in reporting services takes upward of an hour.
> Does anyone have suggestions on how to speed up this report.
> my initial thoughts would be to use SSRS to populate the parameters
> and maybe hook into the reporting engine and execute the stored
> procedure directly with the parameters defined
>
> thanks in advance
> Ian|||An additional point, BCP is the tool to use (not SSIS and not RS) if
performance is your goal. Give a DBA your query used by the report and they
could have BCP written and scripted for you in no time. It's been over a
decade since I used BCP and I had forgotten about it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eES$LTZPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Do not waste your time trying to run the report different ways, it will
> not affect the time.
> Your time is coming in two places. One, does it really only take 5 minutes
> to extract 5 million records? I'm not sure how it works when executing a
> stored procedure from Query Analyzer that returns 5 million records,
> whether it really retrieves all the records or not. Anyway, I think that
> is one area, the number of records being physically returned takes time.
> Second, RS 2000 and RS 2005 does all rendering in RAM. It does not page
> anything out to disk or utilize the disk at all when rendering. I am
> pretty sure this is changing substantially in RS 2008. Adding more RAM to
> the server will help.
> The other option is to use SSIS instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <emery.ian@.googlemail.com> wrote in message
> news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>>I have a report that produces over 3 million rows, this report is
>> export to CSV and is scheduled to run weekly.
>> Sometimes the report is needed to run manually. Initially this report
>> timed out, now the report runs but takes a very long time to complete.
>> If the stored procedure is run directly it takes about 5 mins to
>> execute. Running in reporting services takes upward of an hour.
>> Does anyone have suggestions on how to speed up this report.
>> my initial thoughts would be to use SSRS to populate the parameters
>> and maybe hook into the reporting engine and execute the stored
>> procedure directly with the parameters defined
>>
>> thanks in advance
>> Ian
>

Data driven subscriptions and scale-out?

Say I have a scheduled data-driven reports that will product pdf files (a lot). Will this benefit
from scale-out?

I.e., having one SQL Server with the reporting services databases and several
servers with the reporting services windows service and web services.

I have this feeling that when the Agent job starts, it will do this in a matter so that all the
reports for this job will be processes on only one RS server? Or perhaps I can partition this
myself, where I somehow have several jobs, each job connecting to a different server generating a
subset of the reports?

No replies so I tested it myself. After lots of hassle to get scale-out installed, but I finally got it working. And the answer is Yes.

I processed a data-driven subscription to generate bunch of pdf files and CPU went up on both RS machines. Also looking in the RS log files I can see that each RS machine processed different PDF files.

Data driven subscriptions and scale-out?

Say I have a scheduled data-driven reports that will product pdf files (a lot). Will this benefit
from scale-out?

I.e., having one SQL Server with the reporting services databases and several
servers with the reporting services windows service and web services.

I have this feeling that when the Agent job starts, it will do this in a matter so that all the
reports for this job will be processes on only one RS server? Or perhaps I can partition this
myself, where I somehow have several jobs, each job connecting to a different server generating a
subset of the reports?

No replies so I tested it myself. After lots of hassle to get scale-out installed, but I finally got it working. And the answer is Yes.

I processed a data-driven subscription to generate bunch of pdf files and CPU went up on both RS machines. Also looking in the RS log files I can see that each RS machine processed different PDF files.

Data Driven Subscriptions

Hi !!
I want to create a scheduled delivery, with email & file share used together
with a single execution on a report. This means that i will sent the report
attachment thru an email to the recipient, and the same time copy that report
using the file share in a folder. Is this possible to implement?
This is because, I can only execute the report only once due to an updation
being done to the database.
Thanks - PeteSubscriptions only support sending through 1 delivery extension. Have you
looked into setting up the report to run on an Execution snapshot? This
would allow you to control when the report will run and all renderings of
the report will come off the snapshot and not the data. You could then
create two subscriptions running off either a shared schedule of when the
report execution snapshot is updated.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:36D77E8B-1768-44AA-955E-D459516C7D41@.microsoft.com...
> Hi !!
> I want to create a scheduled delivery, with email & file share used
together
> with a single execution on a report. This means that i will sent the
report
> attachment thru an email to the recipient, and the same time copy that
report
> using the file share in a folder. Is this possible to implement?
> This is because, I can only execute the report only once due to an
updation
> being done to the database.
> Thanks - Pete

Data Driven subscription not working for PDF, IMAGE

Hi,
I worked on SQL Server tutorials in Books online, "Data Driven
Subscription". In this tutorial, reports are delivered via email on a
scheduled time. as per the tutorial emails are being delivered in "MHTML"
format but it does not deliver the report in "PDF" or "Image" format. The
report manager shows a status message that there was an error but there is
no way to know the exact error. It just says "1 errors". The reprot is
working absolutely fine as it is being delivered in "MHTML" format but not
in "PDF" or "IMAGE". Please assist.
Regards
GinnyJust check in errorlog file for any specific errors. Just manually run the
same report and see whether it gives the same error.
Amarnath
"Ginny" wrote:
> Hi,
> I worked on SQL Server tutorials in Books online, "Data Driven
> Subscription". In this tutorial, reports are delivered via email on a
> scheduled time. as per the tutorial emails are being delivered in "MHTML"
> format but it does not deliver the report in "PDF" or "Image" format. The
> report manager shows a status message that there was an error but there is
> no way to know the exact error. It just says "1 errors". The reprot is
> working absolutely fine as it is being delivered in "MHTML" format but not
> in "PDF" or "IMAGE". Please assist.
> Regards
> Ginny
>
>

Data driven subscription

Hi,
I worked on SQL Server tutorials in Books online, "Data Driven
Subscription". In this tutorial, reports are delivered via email on a
scheduled time. as per the tutorial emails are being delivered in "MHTML"
format but it does not deliver the report in "PDF" or "Image" format. The
report manager shows a status message that there was an error but there is
no way to know the exact error. It just says "1 errors". The reprot is
working absolutely fine as it is being delivered in "MHTML" format but not
in "PDF" or "IMAGE". Please assist.
Regards
GinnyHi Ginny,
To know the exact error you will have to look into the ReportServer log
file; its default location is
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\LogFiles
Look at the latest ReportServerService__<time stamp>.log file for
description of the error.
Virat
Ginny wrote:
> Hi,
> I worked on SQL Server tutorials in Books online, "Data Driven
> Subscription". In this tutorial, reports are delivered via email on a
> scheduled time. as per the tutorial emails are being delivered in "MHTML"
> format but it does not deliver the report in "PDF" or "Image" format. The
> report manager shows a status message that there was an error but there is
> no way to know the exact error. It just says "1 errors". The reprot is
> working absolutely fine as it is being delivered in "MHTML" format but not
> in "PDF" or "IMAGE". Please assist.
> Regards
> Ginny

Daily scheduled job has its schedule disabled despite successful completion

I have a job which is scheduled to run once daily. The job is enabled and the schedule is also enabled. After the job runs (successfully), the schedule has its 'Enabled' flag reset (i.e. the 'Enabled' checkbox in the schedule properties has become unchecked) and so is not rescheduled.

I have other jobs configured in a similar way, but they are run and then rescheduled in the normal way without any problems.

What could be going on?I'm sure you'd know but there is definately no disables step in the job?
e.g
EXEC sp_update_job @.job_name = 'bla bla',
@.enabled = 0|||This may be of some use to you. Have you checked the logs?

http://support.microsoft.com/default.aspx?scid=kb;en-us;295378&Product=sql2k