Get a timeout error when trying to export to excel

Hi I generate a report successfully in SQL reporting services . Once
the report is generated I then select the export option to export it
to excel
I then get this error message( see below) after waiting about 20 mins
for the excel file to be generated.
There is a lot of data in this report . There is 1240 pages and there
is about 50 rows for each page. That makes it 62000 rows in the
report.
I'm using excel 2007 on my pc. I've updated sql server 2005 to sp2.
Is there some limitation to the amount of data you can export to
excel.'
thanks in advance
Aidan Geraghty
Server Error in '/Reports' Application.
________________________________________
For more information about this error navigate to the report server on
the local server machine, or enable remote errors
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Exception: For more information about this
error navigate to the report server on the local server machine, or
enable remote errors
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace
below.
Stack Trace:
[Exception: For more information about this error navigate to the
report server on the local server machine, or enable remote errors]
[Exception: An error occurred during rendering of the report.]
[Exception: The operation has timed out. The report server has
canceled the operation.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean
isAbortable, String url, Stream outputStream, String& mimeType,
String& fileNameExtension) +489
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean
isAbortable, String format, String deviceInfo, NameValueCollection
urlAccessParameters, Stream reportStream, String& mimeType, String&
fileNameExtension) +958
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String
format, String deviceInfo, NameValueCollection additionalParams,
String& mimeType, String& fileExtension) +84
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection
urlQuery, HttpResponse response) +143
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
+154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
Boolean& completedSynchronously) +64
________________________________________
Version Information: Microsoft .NET Framework Version:2.0.50727.42;
ASP.NET Version:2.0.50727.210
When I look at the log files on the server I find this error message:
2007-03-30 14:12:44 65.53.207.181 50411 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:17:43 65.53.206.137 21323 10.251.129.151 80 HTTP/1.1
GET /Reports/Reserved.ReportViewerWebControl.axd?
ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=Export&FileName=RebateCoopFundsUploadSixMonthsReport&ContentDisposition=OnlyHtmlInline&Format=EXCEL
- 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:19:13 65.53.206.137 21501 10.251.129.151 80 HTTP/1.1
GET /reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:23:42 65.53.207.181 50419 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
2007-03-30 14:24:54 10.253.212.85 3385 10.253.212.85 80 HTTP/1.1 GET /
ReportServer?%2fReports
%2fRebateCoopFundsUploadSixMonthsReport&rs:SessionID=refs1e553cirzg45tdvejtuw&rs:command=Render&rs:Format=EXCEL&rc:Toolbar=false&rs:ErrorResponseAsXml=true&rs:AllowNewSessions=false
- 478678319 Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 65.53.206.137 21338 10.251.129.151 80 HTTP/1.1
GET /Reports/Reserved.ReportViewerWebControl.axd?
ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Fri
%20Mar%2030%2014%3A49%3A52%202007 - 478678319
Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 10.251.129.151 3419 10.251.129.151 80 HTTP/1.1
POST /reports/ReportService2005.asmx - 478678319
Connection_Abandoned_By_AppPool DefaultAppPool
2007-03-30 14:24:54 65.53.207.181 50461 10.251.129.151 80 HTTP/1.1
GET /Reports/ - 478678319 Connection_Abandoned_By_AppPool
DefaultAppPool
2007-03-30 14:27:09 65.53.206.137 21553 10.251.129.151 80 - - - - -
Timer_ConnectionIdle -
2007-03-30 15:07:17 10.253.212.85 3710 10.253.212.85 80 - - - - -
Timer_ConnectionIdle -
2007-03-30 15:07:17 10.253.212.85 3714 10.253.212.85 80 - - - - -
Timer_ConnectionIdle -On Apr 2, 9:04 am, aidan.gerag...@.gmail.com wrote:
> Hi I generate a report successfully in SQL reporting services . Once
> the report is generated I then select the export option to export it
> to excel
> I then get this error message( see below) after waiting about 20 mins
> for the excel file to be generated.
> There is a lot of data in this report . There is 1240 pages and there
> is about 50 rows for each page. That makes it 62000 rows in the
> report.
> I'm using excel 2007 on my pc. I've updated sql server 2005 to sp2.
> Is there some limitation to the amount of data you can export to
> excel.'
> thanks in advance
> Aidan Geraghty
> Server Error in '/Reports' Application.
> ________________________________________
> For more information about this error navigate to the report server on
> the local server machine, or enable remote errors
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.Exception: For more information about this
> error navigate to the report server on the local server machine, or
> enable remote errors
> Source Error:
> An unhandled exception was generated during the execution of the
> current web request. Information regarding the origin and location of
> the exception can be identified using the exception stack trace
> below.
> Stack Trace:
> [Exception: For more information about this error navigate to the
> report server on the local server machine, or enable remote errors]
> [Exception: An error occurred during rendering of the report.]
> [Exception: The operation has timed out. The report server has
> canceled the operation.]
> Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean
> isAbortable, String url, Stream outputStream, String& mimeType,
> String& fileNameExtension) +489
> Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean
> isAbortable, String format, String deviceInfo, NameValueCollection
> urlAccessParameters, Stream reportStream, String& mimeType, String&
> fileNameExtension) +958
> Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String
> format, String deviceInfo, NameValueCollection additionalParams,
> String& mimeType, String& fileExtension) +84
> Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection
> urlQuery, HttpResponse response) +143
> Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext
> context) +75
> System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
> +154
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
> Boolean& completedSynchronously) +64
> ________________________________________
> Version Information: Microsoft .NET Framework Version:2.0.50727.42;
> ASP.NET Version:2.0.50727.210
> When I look at the log files on the server I find this error message:
> 2007-03-30 14:12:44 65.53.207.181 50411 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:17:43 65.53.206.137 21323 10.251.129.151 80 HTTP/1.1
> GET /Reports/Reserved.ReportViewerWebControl.axd?
> ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
> df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=Export&FileName=RebateCoopFundsUploadSixMonthsReport&ContentDisposition=OnlyHtmlInline&Format=EXCEL
> - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:19:13 65.53.206.137 21501 10.251.129.151 80 HTTP/1.1
> GET /reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:23:42 65.53.207.181 50419 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Dropped DefaultAppPool
> 2007-03-30 14:24:54 10.253.212.85 3385 10.253.212.85 80 HTTP/1.1 GET /
> ReportServer?%2fReports
> %2fRebateCoopFundsUploadSixMonthsReport&rs:SessionID=refs1e553cirzg45tdvejtuw&rs:command=Render&rs:Format=EXCEL&rc:Toolbar=false&rs:ErrorResponseAsXml=true&rs:AllowNewSessions=false
> - 478678319 Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 65.53.206.137 21338 10.251.129.151 80 HTTP/1.1
> GET /Reports/Reserved.ReportViewerWebControl.axd?
> ReportSession=refs1e553cirzg45tdvejtuw&ControlID=ec48b970-2dd3-438c-9b98-
> df1db9c941ab&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Fri
> %20Mar%2030%2014%3A49%3A52%202007 - 478678319
> Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 10.251.129.151 3419 10.251.129.151 80 HTTP/1.1
> POST /reports/ReportService2005.asmx - 478678319
> Connection_Abandoned_By_AppPool DefaultAppPool
> 2007-03-30 14:24:54 65.53.207.181 50461 10.251.129.151 80 HTTP/1.1
> GET /Reports/ - 478678319 Connection_Abandoned_By_AppPool
> DefaultAppPool
> 2007-03-30 14:27:09 65.53.206.137 21553 10.251.129.151 80 - - - - -
> Timer_ConnectionIdle -
> 2007-03-30 15:07:17 10.253.212.85 3710 10.253.212.85 80 - - - - -
> Timer_ConnectionIdle -
> 2007-03-30 15:07:17 10.253.212.85 3714 10.253.212.85 80 - - - - -
> Timer_ConnectionIdle -
I'm not sure about a limitation in MS Excel 2007; but, in MS Excel
2003, the limit was somewhere around 65000 rows. In my opinion, it
sounds like it might be too much data to stream into Excel. Sorry I
could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Gerating Excel Reports from SSIS

I want to be able to create an Excel report in SSIS after querying the data from a SQLSERVER table.

I have a IS package where I'm loading all the data required in the report and the final step of the IS package I would like to build the reports. I think it makes sense to take this approach instead of setting up a RSS package.

AS anyone seen any Blogs which explains such a flow?

You can use an Excel destination in your data flow, but beyond that, you won't be able to apply unique formatting rules, grid lines, images, charts, etc...

Not without programming a script component, anyway.

generating sql logins

I've been given an excel file that lists:
userID's,
Names
Password
expiary dates.
I need to convert this list into sql server logins. Can this be done via a tsql statement? if so how, and if it cant be done via a statement then what other way can it be done.

Many thanks in advance.

Air:

I would probably use SSIS for this; however, you probably can also put together a script that uses BULK INSERT to get this done. This will be more simple if you can just ram and jam because none of these logins pre-exist.

Dave

|||

use master

declare @.loginame sysname

set @.loginame=N'DOMAIN\user'

exec sp_grantlogin @.loginame

exec sp_addsrvrolemember @.loginame, sysadmin

--

... most probably, NOT sysadmin, but whatever you need for that particular John or Jane Doe...

generating Excel file every morning

Hi, I started playing with reporting services, followed the first 3 lessons
int the walkthrough, I find it a very interesting tool.
When viewing a report in IE, we have the possibility to export to excel
file. I woudl like to do a dynamic report, let's say generated on the fly at
6 o'clock morning and send it by mail as attachment. Is it possible with
reportin g services? if yes, any good articles or tutorials somewhere
Thanks for your helpYes, this is possible. Look for subscriptions and scheduling in the BOL.
You can also start here with the How Tos:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_overview_v1_46r7.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"SalamElias" <eliassal@.online.nospam> wrote in message
news:19540F06-2F01-466E-A93E-28C19A763BA3@.microsoft.com...
> Hi, I started playing with reporting services, followed the first 3
lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly
at
> 6 o'clock morning and send it by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
>|||Hi SalamElias,
I come across with the same situation as you mentioned in this post. Any
comment or idea you could provide? Could you share with me the
approaches/methods you taken for the task? Thanks a lot
"SalamElias" wrote:
> Hi, I started playing with reporting services, followed the first 3 lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly at
> 6 o'clock morning and send it by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
>

generating excel file automatically every morning

Hi, I started playing with reporting services, followed the first 3 lessons
int the walkthrough, I find it a very interesting tool.
When viewing a report in IE, we have the possibility to export to excel
file. I woudl like to do a dynamic report, let's say generated on the fly at
6 o'clock morning and sent by mail as attachment. Is it possible with
reportin g services? if yes, any good articles or tutorials somewhere
Thanks for your help
What do you mean by dynamic?
You can schedule a report to run by using the Subscriptions "tab."
You can drop the report on a file share or you can email it.
By the way, you might want to address future questions related to Reporting
Services to the reporting services newsgroup
microsoft.public.sqlserver.reportingsvcs
Keith
"SalamElias" <eliassal@.online.nospam> wrote in message
news:59042B1C-ABC6-43D0-B0F7-BA82E442D298@.microsoft.com...
> Hi, I started playing with reporting services, followed the first 3
lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly
at
> 6 o'clock morning and sent by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
|||By dynamic I mean, the report is executed for example 5 times, each time a
where clause containing a different value in order to generate 5 different
reports(I have to send an email for each provider with product I have sold
belonging to him)
In the doc on line, I saw we can generate we can have 2 datasets, the result
in the main table can be changed according to a parameter.
Thanks for your help. Next question I will post to it to the appropriate group
"Keith Kratochvil" wrote:

> What do you mean by dynamic?
> You can schedule a report to run by using the Subscriptions "tab."
> You can drop the report on a file share or you can email it.
> By the way, you might want to address future questions related to Reporting
> Services to the reporting services newsgroup
> microsoft.public.sqlserver.reportingsvcs
> --
> Keith
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:59042B1C-ABC6-43D0-B0F7-BA82E442D298@.microsoft.com...
> lessons
> at
>
|||If you have the Enterprise Edition of RS, use a data driven subscription for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:44BA40B6-897F-4963-998B-9A5E137664A5@.microsoft.com...[vbcol=seagreen]
> By dynamic I mean, the report is executed for example 5 times, each time a
> where clause containing a different value in order to generate 5 different
> reports(I have to send an email for each provider with product I have sold
> belonging to him)
> In the doc on line, I saw we can generate we can have 2 datasets, the result
> in the main table can be changed according to a parameter.
> Thanks for your help. Next question I will post to it to the appropriate group
> "Keith Kratochvil" wrote:

Generating Custom Messages to User

Hello everyone,

i'm using a foreach loop container to read a group of excel files and pass their information to a Sql Server database. The process runs well but sometimes there could be some excel files that may not be processed correctly so i'm using transacctions to continue to process on the other files, But i'd like to generate a message everytime an excel file is or not processed. I thought that i could generate a flat file to do it, but is there any other way to accomplish this? I'm also generating a log file (on xml format), but It seems too much information for an end-user.

any suggestions?

regards.

You can generate messages that automatically get captured by your log provider. Is this what you want to do?

-Jamie

|||Hello Jamie

I'm actually using a log provider, so i get the log of the progress.

My solution was to get into the log (which is an xml output file) two kind of messages: the first one, an script that Logs a new "Sucess file" message and another script task which says "Failure process file". So at the end of the tasks i need to run, i get just one of the script tasks to log into my file.

But is there any other way to do it?.

regards