April 23, 2009 at 7:02 am
Hello all, Im new on the Site, Kevin is the name and based in Ireland looking after some SQL Servers around the Globe.
I have one clients that uses The Reporting Services. We have one particulare Report, a Sales Report that is driven by the Sales Rep Parameter.
We have many subscription set up to email the report in excel for a specified REP and we then email the REP his Report basically.
There is two Reps that the subscriptions are set up for that will not email the EXCEL Version of the Report. Its a big report and to run it diret it takes some time. We use Caching also but still slow. It will email if we use PDF or the Link, but excel NO. And the Sub Status shows Email Sent.....but nothing...
Any pointers?
April 23, 2009 at 11:54 am
Does the email arrive, but without the attachment? Or does the email not arrive at all?
What is the email client? Is there an option setting for those two that disallows .XLS files?
What if you change the email address for those subscriptions to another email address (like yours)? Does it send to you appropriately?
It sounds to me like some security setting on those particular salesmen's computers is the problem.
But I am flying blind... 😉
April 23, 2009 at 1:52 pm
Is it possible that the reports are exceeding the limitations of Excel? How many rows are being returned for these salesmen?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 3:11 am
The Reps in question are external entities with External Email, like the other 8 Reps.
This issue is with the subscription itself when selecting EXCEL. It does not work for any email address.
It actually does not work for CSV either. But it works for all the others.
The Status is Email Sent...., but nothing hits or leaves the exchange server.
Looking at the Report Database, for that Subscription Execution, the processing fields (times to retrieve data etc) all show 0..
Thanks Guys
April 24, 2009 at 3:13 am
These two Reps are would be the biggest turnover, so would be the largest files.
The Spreadsheet if I manyall export from the Report are about 10MB.
I did think the Exchange Server Limits were blocking the sending, but I have no limits now but still same issue.
It must be todo with the Size, but Im lost in how to find it.
Is there a log file anywhere that would show anything>?
April 27, 2009 at 7:11 am
10MB is too large for email if you know your etiquette. A filter someplace is going to stop that transmission, if your not, then your client is. You need to look for another way to send your data.
I personally would setup an SSIS package and FTP the data.
April 27, 2009 at 7:46 am
Hi Jason,
The Report is very large and right now the Reps get it via a subscription. There is no filtering on the apps server and it sends out using the exchange server with out limits for testing. It dont even hit the exchange server. The ReporServer execurtion table seems odd to me, it is not even processing? Is there timeout setting anywhere for subscriptions reading data from the server?
It would be good to get it Zipped before it is emailed, but Im not sure if that is possible using a subscription.
With SSIS Package, can I set it up to run for a Rep and then Zip it up and Email?
Thanks.
April 27, 2009 at 8:12 am
There is no time-out in RS by default. If you go into the reports subscription tab is should tell you whether it was successful or if it failed.
You should be tracking your report usage. Here is a basic script that can get you started (be sure to change the "ReportServer" database name to whatever you named yours):
SELECT
C.[Name]
,EL.[UserName]
,EL.[Format]
,EL.[TimeStart]
,EL.[TimeEnd]
,EL.[RowCount]
,EL.[Status]
,(
SELECT DATEDIFF(mi, TimeStart, TimeEnd)
) as MinutesRan
FROM ReportServer.dbo.ExecutionLog EL
INNER JOIN ReportServer.dbo.Catalog C
ON EL.ReportID = C.ItemID
ORDER BY EL.TimeStart DESC
And lastly...anything is possible in SSIS!!! :hehe:
April 27, 2009 at 8:33 am
Jason, thanks again for swift reply.
The SQL Code is handy to have, thanks.
Here is the Result for my Report
1751
Order Status
User Scott
FormatEXCEL
2009-02-27 13:15:44.660
2009-02-27 13:15:47.100
0
rsSuccess
0
April 27, 2009 at 8:37 am
Jason Appologies for last post I hit the Post too soon.
The Results when I select EXCEL for the REP
Name User Format Time Start Time End Row Count Status Minutes Ran
Order Status Scott EXCEL 2009-02-27 13:15:44.660 2009-02-27 13:15:47.100 0 rsSuccess 0
When I use the HTML
Name User Format Time Start Time End Row Count Status Minutes Ran
Order Status Scott HTML4.0 2009-02-27 13:46:13.523 2009-02-27 13:46:17.307 283 rsSuccess 0
So when I select Excel for these two Reps it seems not to even select any data? Any Clues
April 27, 2009 at 11:29 am
If it could create the excel document for any reason you would see a failure in the results brought back.
My next suggestion would be to run the process manually and see what happen. Can you run the report? Next can you export the file to excel manually? Next can you email it to a co-worker? And so on...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply