sql login permissions: procedure

Showing posts with label procedure. Show all posts

Wednesday, March 28, 2012

long running report can not be rendered in Report Manager or through Subscription

I am using a stored procedure which will run about one hour and ten minutes to return a dataset to reporting services to generate a report. The report generation is OK if I run it inside Visual Studio 2005. Once it is deployed, it can not finish. Both cases I set "Do not timeout report execution". I even try to create a subscription to let it run throught. But it still can not finish. The traces information in ReportServer*.Log is like this:

<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.1399.00</Product>
<Locale>en-US</Locale>
<TimeZone>Central Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles\ReportServer__06_29_2006_09_58_21.log</Path>
<SystemName>JSONG-XP</SystemName>
<OSName>Microsoft Windows NT 5.1.2600 Service Pack 2</OSName>
<OSVersion>5.1.2600.131072</OSVersion>
</Header>
aspnet_wp!webserver!6!6/29/2006-09:58:21:: i INFO: Reporting Web Server started
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
aspnet_wp!configmanager!6!6/29/2006-09:58:21:: w WARN: WebServiceAccount is not specified in the config file. Using default: JSONG-XP\ASPNET
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
aspnet_wp!resourceutilities!6!6/29/2006-09:58:21:: i INFO: Reporting Services starting SKU: Developer
aspnet_wp!resourceutilities!6!6/29/2006-09:58:21:: i INFO: Evaluation copy: 0 days left
aspnet_wp!runningjobs!6!6/29/2006-09:58:21:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds
aspnet_wp!runningjobs!6!6/29/2006-09:58:21:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
aspnet_wp!runningjobs!6!6/29/2006-09:58:21:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
aspnet_wp!runningjobs!6!6/29/2006-09:58:21:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
aspnet_wp!library!6!06/29/2006-09:58:23:: i INFO: Call to GetPermissions:/
aspnet_wp!library!6!06/29/2006-09:58:24:: i INFO: Catalog SQL Server Edition = Developer
aspnet_wp!library!6!06/29/2006-09:58:24:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-09:58:40:: i INFO: Call to GetPermissions:/CreditReports
aspnet_wp!library!6!06/29/2006-09:58:40:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!1!06/29/2006-09:58:41:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!1!06/29/2006-09:58:41:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!1!06/29/2006-09:58:44:: i INFO: Call to RenderFirst( '/CreditReports/ExperienceByCoverageSEDEV' )
aspnet_wp!runningjobs!5!6/29/2006-09:59:27:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:03:36:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:06:25:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:06:25:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:13:12:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:16:12:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:16:12:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:23:13:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:26:13:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:26:13:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:33:11:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:36:09:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:36:09:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:43:06:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:46:05:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:46:05:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:53:07:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:56:07:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:56:07:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!a!6/29/2006-10:57:07:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
aspnet_wp!runningjobs!c!6/29/2006-10:58:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-10:58:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-10:59:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-10:59:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:00:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:00:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:01:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:01:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:02:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:02:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:03:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:03:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:04:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:04:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:05:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:05:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!library!c!6/29/2006-11:06:07:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams
aspnet_wp!runningjobs!c!6/29/2006-11:06:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:06:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!session!9!06/29/2006-11:06:52:: e ERROR: Sql Error in GetSnapshotData: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader()
at Microsoft.ReportingServices.Library.DatabaseSessionStorage.GetSessionData(String sessionId, String userName, ConnectionManager connMgr)
aspnet_wp!library!9!06/29/2006-11:06:52:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. > System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader()
at Microsoft.ReportingServices.Library.DatabaseSessionStorage.GetSessionData(String sessionId, String userName, ConnectionManager connMgr)
at Microsoft.ReportingServices.Library.DatabaseSessionStorage.GetSessionData(String sessionId, String userName)
at Microsoft.ReportingServices.Library.SessionReportItem.Load(DatabaseSessionStorage sessionStorage, LoadAction loadFlags, String sessionId, String reportPath, DateTime snapshotDate, String userName, String userParams, String imageName, DatasourceCredentialsCollection dataSourceCred)
at Microsoft.ReportingServices.WebServer.HttpClientRequest.LoadFromDB(LoadAction loadFlags, CatalogItemContext context)
at Microsoft.ReportingServices.WebServer.SessionStarterAction.CreateExisting()
End of inner exception stack trace
aspnet_wp!library!6!06/29/2006-11:06:54:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!6!06/29/2006-11:06:54:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-11:06:55:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!6!06/29/2006-11:06:55:: i INFO: Call to GetSystemPermissions
aspnet_wp!runningjobs!5!6/29/2006-11:07:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!5!6/29/2006-11:07:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!library!8!06/29/2006-11:07:51:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:07:51:: i INFO: Call to GetSystemPermissions
aspnet_wp!runningjobs!5!6/29/2006-11:08:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!5!6/29/2006-11:08:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!library!8!06/29/2006-11:08:16:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:08:16:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!8!06/29/2006-11:08:16:: i INFO: Call to GetSnapshotLimit( '/CreditReports/ExperienceByCoverageSEDEV' )
aspnet_wp!library!6!06/29/2006-11:08:19:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!6!06/29/2006-11:08:19:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-11:08:19:: i INFO: Call to GetPolicies:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:08:21:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:08:22:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-11:08:28:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!6!06/29/2006-11:08:28:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!8!06/29/2006-11:08:37:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:08:37:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-11:08:51:: i INFO: Call to GetPermissions:/CreditReports
aspnet_wp!library!6!06/29/2006-11:08:51:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!8!06/29/2006-11:08:55:: i INFO: Call to GetPermissions:/CreditReports/AS400SEDEV
aspnet_wp!library!8!06/29/2006-11:08:55:: i INFO: Call to GetSystemPermissions
aspnet_wp!runningjobs!5!6/29/2006-11:09:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!5!6/29/2006-11:09:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...

I would like know the reason behind it. How to fix that?. I use Sql Server 2005 developer edition.

Thanx

There is a limit for how long a report will run from Report Manager. You can modify the setting at:

Report Manager --> Site Settings-->Report Execution Timeout-->Limit
report execution to the following number of seconds = 3600 (1 hr)

|||

Thank you for your reply.

Actually I set

Report Manager --> Site Settings-->Report Execution Timeout-->Do not timeout report execution. It still can not produce the report.

Another question is after I set localhost/reports and localhost/reportserver session timeout to 3 hrs and these site's asp.net state management session timout to 3hrs, the localhost/reportserver is not accessable. Maybe it is a reporting services bug. Steps to repeat the scenario: Go to IIS, right click localhost/reports-->Properties-->Click Configuration-->Application Configuration-->Options-->Change session timeout to 180 minutes. To set asp.net state management session timout to 3hrs. right click localhost/reports-->Properties-->Report Properties windows-->Click ASP.NET tab-->Edit Configuration-->State Management-->Set Session timout to 180 Minutes.

So far so good. Once repeating above operations to localhost/reportserver, both sites will become unaccessable.

Your response will be appreciated.

|||

The exception you are hitting is the session access timeout. The viewer control will try to ping back to the server every N-1 minutes (where N is basically the session expiration time). The default session access timeout is 10 minutes, so if the report takes over an hour to render, you will hit this timeout.

You can run into problems when the time it takes to render the report is longer than the session timeout, I would recommend altering the following system properties (you will have to do this via the SOAP API or an rs.exe script since they are not exposed in the management studio UI) to be more on the order of what you expect the report execution to be:

SessionAccessTimeout|||

Actually, rendering the long running report in the report manager is just for test purpose. I will create a subscription to deliver the report to a shared folder using SOAP API. Even for the subscription, the timeout issue still exists. Could you provide me some examples to set session timeout via SOAP API or a re.exe.

Thank you very much.

|||Any news? I have the same issue. Can you explain please which values should be used for these parameters in order to get the report running for more than 1 hour?|||There is a blog about it here

http://blogs.msdn.com/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx
So i did change this for a report that needs 20 minutes but it still fail after 5 minutes with this error in the computer application log
Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 10/31/2006 10:08:46 AM
Event time (UTC): 10/31/2006 5:08:46 PM
Event ID: c6233c9a2cff498a90cfa4140a3e932a
Event sequence: 1572
Event occurrence: 1
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/Reports-1-128067603886462532
Trust level: RosettaMgr
Application Virtual Path: /Reports
Application Path: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\
Machine name: ATLANTA
Process information:
Process ID: 6452
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: ReportServerException
Exception message: Execution 'fmnjra45tvj15545rxlxt5ec' cannot be found (rsExecutionNotFound)
Request information:
Request URL: http://reporting.onsemi.com/Reports/Reserved.ReportViewerWebControl.axd?ReportSession=fmnjra45tvj15545rxlxt5ec&ControlID=84b2a2bd-4f03-4c5d-9d73-c305e7883b6e&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl161TouchSession0&CacheSeed=Tue Oct 31 10:08:44 2006
Request path: /Reports/Reserved.ReportViewerWebControl.axd
User host address: 10.253.138.208
User: ONSEMI\R38851
Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 14
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: True
Stack trace: at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.SetExecutionId(String executionId, Boolean fullReportLoad)
at Microsoft.Reporting.WebForms.ServerReport.LoadFromUrlQuery(NameValueCollection requestParameters, Boolean fullReportLoad)
at Microsoft.Reporting.WebForms.ReportDataOperation..ctor()
at Microsoft.Reporting.WebForms.HttpHandler.GetHandler()
at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Any idea?
Thanks,
Philippe

long running report can not be rendered in Report Manager or through Subscription

I would like know the reason behind it. How to fix that?. I use Sql Server 2005 developer edition.

Thanx

There is a limit for how long a report will run from Report Manager. You can modify the setting at:

Report Manager --> Site Settings-->Report Execution Timeout-->Limit
report execution to the following number of seconds = 3600 (1 hr)

|||

Thank you for your reply.

Actually I set

Report Manager --> Site Settings-->Report Execution Timeout-->Do not timeout report execution. It still can not produce the report.

So far so good. Once repeating above operations to localhost/reportserver, both sites will become unaccessable.

Your response will be appreciated.

|||

SessionAccessTimeout|||

Thank you very much.

|||Any news? I have the same issue. Can you explain please which values should be used for these parameters in order to get the report running for more than 1 hour?|||There is a blog about it here

Any idea?
Thanks,
Philippe

long running report can not be rendered in Report Manager or through Subscription

I would like know the reason behind it. How to fix that?. I use Sql Server 2005 developer edition.

Thanx

There is a limit for how long a report will run from Report Manager. You can modify the setting at:

Report Manager --> Site Settings-->Report Execution Timeout-->Limit
report execution to the following number of seconds = 3600 (1 hr)

|||

Thank you for your reply.

Actually I set

Report Manager --> Site Settings-->Report Execution Timeout-->Do not timeout report execution. It still can not produce the report.

So far so good. Once repeating above operations to localhost/reportserver, both sites will become unaccessable.

Your response will be appreciated.

|||

SessionAccessTimeout|||

Thank you very much.

|||Any news? I have the same issue. Can you explain please which values should be used for these parameters in order to get the report running for more than 1 hour?|||There is a blog about it here

Any idea?
Thanks,
Philippesql

Long Running Queries in Procedure

derek@.programmer.net May 19, 12:57 pm show options
From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
author
Date: Thu, 19 May 2005 09:57:29 -0700
Local: Thurs,May 19 2005 12:57 pm
Subject: Long Running Queries in Procedure
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse
Hi,
Has anyone ever seen a query take longer in a stored procedure than it
does when running in Query Analyzer? I have a set of about 10 queries
that run for 18 minutes when running in a schedule job (which runs a
stored procedure) but only for about 4 minutes when I run them manually
in Query Analyzer.
I'm trying to trim time out of a daily procedure that takes about 3
hours and I've already done some performance tuning - that's why I know
how long they take in Query Analyzer. However, when I time how long
the individual portions of the proc run - I get different results than
those I receive when I run the queries myself.
Any thoughts?
DerekUse SQL Server Profiler to monitor performance related events. Whatever, the
issue it will turn up there.
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> wrote in message
news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
> derek@.programmer.net May 19, 12:57 pm show options
> From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
> author
> Date: Thu, 19 May 2005 09:57:29 -0700
> Local: Thurs,May 19 2005 12:57 pm
> Subject: Long Running Queries in Procedure
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> Hi,
>
> Has anyone ever seen a query take longer in a stored procedure than it
> does when running in Query Analyzer? I have a set of about 10 queries
> that run for 18 minutes when running in a schedule job (which runs a
> stored procedure) but only for about 4 minutes when I run them manually
> in Query Analyzer.
>
> I'm trying to trim time out of a daily procedure that takes about 3
> hours and I've already done some performance tuning - that's why I know
> how long they take in Query Analyzer. However, when I time how long
> the individual portions of the proc run - I get different results than
> those I receive when I run the queries myself.
>
> Any thoughts?
>
> Derek
>
>|||Did you looked for locks on tables ? Are your queries very "cost" intensive
or "lock" intensive ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
Newsbeitrag news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
> derek@.programmer.net May 19, 12:57 pm show options
> From: "d...@.programmer.net" <d...@.programmer.net> - Find messages by this
> author
> Date: Thu, 19 May 2005 09:57:29 -0700
> Local: Thurs,May 19 2005 12:57 pm
> Subject: Long Running Queries in Procedure
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> Hi,
>
> Has anyone ever seen a query take longer in a stored procedure than it
> does when running in Query Analyzer? I have a set of about 10 queries
> that run for 18 minutes when running in a schedule job (which runs a
> stored procedure) but only for about 4 minutes when I run them manually
> in Query Analyzer.
>
> I'm trying to trim time out of a daily procedure that takes about 3
> hours and I've already done some performance tuning - that's why I know
> how long they take in Query Analyzer. However, when I time how long
> the individual portions of the proc run - I get different results than
> those I receive when I run the queries myself.
>
> Any thoughts?
>
> Derek
>
>|||JT,
This will probably sound like a stupid question, but I'm unfamiliar with
using Profiler. Do you have any suggestions on how to setup the trace - wha
t
events to add, etc? If not, any suggestions on helpful articles about the
Profiler?
Derek
"JT" wrote:

> Use SQL Server Profiler to monitor performance related events. Whatever, t
he
> issue it will turn up there.
> "Derek Fisher" <DerekFisher@.discussions.microsoft.com> wrote in message
> news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
>
>|||Jens,
I'm not sure how to know. How would I tell?
Derek
"Jens Sü?meyer" wrote:

> Did you looked for locks on tables ? Are your queries very "cost" intensiv
e
> or "lock" intensive ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:E1976E3E-7CF3-4475-8600-6FEFA92370B8@.microsoft.com...
>
>|||You can use the statemtent sp_lock and sp_who and sp_who2 to identify the
blockings issue (if you have one). ANother oprtion would be to monitor the
events in the profiler, just check on the lock properties to monitor. If you
are not familiar with the profiler you can look in the current activitie in
your EM, to see wheter a lock or perhaps deadlocks occured.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Derek Fisher" <DerekFisher@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C80C8BFB-3227-4306-AB80-B7DB2F34B695@.microsoft.com...
> Jens,
> I'm not sure how to know. How would I tell?
> Derek
> "Jens Smeyer" wrote:
>

Long running DTS package

Hello,
I have a DTS package that took 52 hours to run. Within the package is a
task that runs a sproc which is a procedure to calculate premiums recvd and
has to run through over 2 million records. The procedure uses a cursor to
move through the records. My question is - is it possible that there were
memory leaks in the server while the package ran (there are a lot of other
things that go on on that server) making some of the data incorrect and some
of it correct? Because, I can take a portion of the data (a months worth)
and run that same process and it works just fine. Any ideas would help
tremendously.
Thanks,
PatriceMy goodness, that is a long-running process. I've had DTS packages going
through millions of records in a matter of 2-3 hours on an older machine.
There was some cursor activity in those processes as well. My guess is that
with some thoughtful redesign you can eliminate the cursor on a bunch of it.
(Can you precalculate some information and then handle the rest with joins
and aggregate functions?) That will make it easier to debug, as well as
run faster.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:1EC80DAD-CC19-447F-B9B5-A344A5E417E5@.microsoft.com...
> Hello,
> I have a DTS package that took 52 hours to run. Within the package is a
> task that runs a sproc which is a procedure to calculate premiums recvd
> and
> has to run through over 2 million records. The procedure uses a cursor to
> move through the records. My question is - is it possible that there were
> memory leaks in the server while the package ran (there are a lot of other
> things that go on on that server) making some of the data incorrect and
> some
> of it correct? Because, I can take a portion of the data (a months worth)
> and run that same process and it works just fine. Any ideas would help
> tremendously.
>
> Thanks,
> Patrice
>|||On Thu, 29 Dec 2005 07:28:02 -0800, "Patrice"
<Patrice@.discussions.microsoft.com> wrote:
>Hello,
>I have a DTS package that took 52 hours to run. Within the package is a
>task that runs a sproc which is a procedure to calculate premiums recvd and
>has to run through over 2 million records. The procedure uses a cursor to
>move through the records. My question is - is it possible that there were
>memory leaks in the server while the package ran (there are a lot of other
>things that go on on that server) making some of the data incorrect and som
e
>of it correct? Because, I can take a portion of the data (a months worth)
>and run that same process and it works just fine. Any ideas would help
>tremendously.
Can you post the cursor code?
Please look into the forward_only and fast_forward options. When you
have a (default) dynamic cursor, it can scale very poorly.
Josh

Monday, March 26, 2012

Long Oracle report

Hi,
I'm having problems with a report that has a Oracle datasource.
The query source is a stored procedure that takes 3 hours to complete but
reporting services don't wait this time and shows an error page.
Reporting Services is configured to "Do not timeout report execution" and
the report uses "Use default setting"
This works ok with a SLQ Server Stored procedure.
Any ideas?
AndrésHello Andres,
I'd like to know the exact error message you encountered. Also, how long
did it take to display error message after you started to execute the
report? You mentioned, the SP works in SQL but I wonder if the report from
SQL server also takes 3 hours to render?
As I know, IE version 5.x and higher have 60 minute time-out limit. A quick
registry setting and a restart fixes the problem.
1. Add a ReceiveTimeout DWORD value with a data value of (<number
ofseconds>)*1000 in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
For example, if you want the timeout duration to be 8 minutes, set the
ReceiveTimeout data value to 480000 (<480>*1000).
2. Restart your computer.
You can find more info at:
http://support.microsoft.com/kb/q181050/
You could check the datasource of Oracle in RS designer by open the data
source->Edit->Advanced and set connection timeout to 0 (not timeout).
Also, the issue could occur if Oracle disconnect the connection from RS due
to some issues. You may want to include Oracle support on this specific
issue and look into the logs under
C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Andres" <andres.fontan@.alcuadrado.com>
>Subject: Long Oracle report
>Date: Tue, 27 Dec 2005 09:33:15 -0500
>Lines: 21
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65737
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hi,
>I'm having problems with a report that has a Oracle datasource.
>The query source is a stored procedure that takes 3 hours to complete but
>reporting services don't wait this time and shows an error page.
>Reporting Services is configured to "Do not timeout report execution" and
>the report uses "Use default setting"
>This works ok with a SLQ Server Stored procedure.
>Any ideas?
>Andrés
>
>
>
>|||Hello Peter
I tried with this with SQL Server (A report based in a stored procedure with
a waifor command before a select .... ) and it works fine!.
Now we are working with Oracle to find if it works also. The problem is that
each try is 4 hours!
Regards,
Andres
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl...
> Hello Andres,
> I'd like to know the exact error message you encountered. Also, how long
> did it take to display error message after you started to execute the
> report? You mentioned, the SP works in SQL but I wonder if the report from
> SQL server also takes 3 hours to render?
> As I know, IE version 5.x and higher have 60 minute time-out limit. A
> quick
> registry setting and a restart fixes the problem.
> 1. Add a ReceiveTimeout DWORD value with a data value of (<number
> ofseconds>)*1000 in the following registry key:
> HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
> For example, if you want the timeout duration to be 8 minutes, set the
> ReceiveTimeout data value to 480000 (<480>*1000).
> 2. Restart your computer.
> You can find more info at:
> http://support.microsoft.com/kb/q181050/
>
> You could check the datasource of Oracle in RS designer by open the data
> source->Edit->Advanced and set connection timeout to 0 (not timeout).
> Also, the issue could occur if Oracle disconnect the connection from RS
> due
> to some issues. You may want to include Oracle support on this specific
> issue and look into the logs under
> C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
>>From: "Andres" <andres.fontan@.alcuadrado.com>
>>Subject: Long Oracle report
>>Date: Tue, 27 Dec 2005 09:33:15 -0500
>>Lines: 21
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>>X-RFC2646: Format=Flowed; Original
>>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65737
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>Hi,
>>I'm having problems with a report that has a Oracle datasource.
>>The query source is a stored procedure that takes 3 hours to complete but
>>reporting services don't wait this time and shows an error page.
>>Reporting Services is configured to "Do not timeout report execution" and
>>the report uses "Use default setting"
>>This works ok with a SLQ Server Stored procedure.
>>Any ideas?
>>Andrés
>>
>>
>>
>|||Hello Andres,
Thank you for taking time to write in about the status of the issue. :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Andres" <andres.fontan@.alcuadrado.com>
>References: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
<FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Long Oracle report
>Date: Tue, 3 Jan 2006 11:51:19 -0500
>Lines: 111
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <OCz67XIEGHA.1424@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:66018
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hello Peter
>I tried with this with SQL Server (A report based in a stored procedure
with
>a waifor command before a select .... ) and it works fine!.
>Now we are working with Oracle to find if it works also. The problem is
that
>each try is 4 hours!
>Regards,
>Andres
>
>
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl...
>> Hello Andres,
>> I'd like to know the exact error message you encountered. Also, how long
>> did it take to display error message after you started to execute the
>> report? You mentioned, the SP works in SQL but I wonder if the report
from
>> SQL server also takes 3 hours to render?
>> As I know, IE version 5.x and higher have 60 minute time-out limit. A
>> quick
>> registry setting and a restart fixes the problem.
>> 1. Add a ReceiveTimeout DWORD value with a data value of (<number
>> ofseconds>)*1000 in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
>> For example, if you want the timeout duration to be 8 minutes, set the
>> ReceiveTimeout data value to 480000 (<480>*1000).
>> 2. Restart your computer.
>> You can find more info at:
>> http://support.microsoft.com/kb/q181050/
>>
>> You could check the datasource of Oracle in RS designer by open the data
>> source->Edit->Advanced and set connection timeout to 0 (not timeout).
>> Also, the issue could occur if Oracle disconnect the connection from RS
>> due
>> to some issues. You may want to include Oracle support on this specific
>> issue and look into the logs under
>> C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
>> Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> --
>>From: "Andres" <andres.fontan@.alcuadrado.com>
>>Subject: Long Oracle report
>>Date: Tue, 27 Dec 2005 09:33:15 -0500
>>Lines: 21
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>>X-RFC2646: Format=Flowed; Original
>>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:65737
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>Hi,
>>I'm having problems with a report that has a Oracle datasource.
>>The query source is a stored procedure that takes 3 hours to complete but
>>reporting services don't wait this time and shows an error page.
>>Reporting Services is configured to "Do not timeout report execution" and
>>the report uses "Use default setting"
>>This works ok with a SLQ Server Stored procedure.
>>Any ideas?
>>Andrés
>>
>>
>>
>>
>
>

long html string

Hi All
I'm trying to display a html report from a store procedure but the string is
to long. I try to cut the sting in parts but no luck.
CREATE PROCEDURE createReport
@.orderNumber int
AS
declare @.lineDesc01 varchar(8000)
declare @.lineDesc02 varchar(8000)
declare @.lineDesc03 varchar(8000)
set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
<td > ''+ cast(b.orderID as varchar) +''</td> bla...'
set @.lineDesc02 = '</td>
<td>''+ isnull(b.myDesc, '') +''</td>
</tr>
</table><br> bla.bla...
set @.lineDesc03 = '</td>
<td>como esta</td>
</tr>
</table></body></html>''
FROM orders b
WHERE b.orderID = @.orderNumber'
exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
How can i fix this problem?
Is other way to do this?
Tks in advance.
JFBWhy are you creating the presentation (HTML) in the stored procedure? You
should always try to minimize the amount of data you pass server <-> client
and you should leave as much of the presenation as you can where it belongs,
at the presentation layer.
But aside from that, why are you using variables at all?
SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
'</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
'</td><td>como esta...up to about 8000'
FROM orders
WHERE orderID = @.orderNumber
Now the presentation tier just has to write out however many columns you end
up with (and I only suggest using columns because you need to concatenate).
There are much, much, much better was to do this, even if you are going to
insist on the bad practice of missing data operations with presentation
details...
"JFB" <help@.jfb.com> wrote in message
news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I'm trying to display a html report from a store procedure but the string
> is to long. I try to cut the sting in parts but no luck.
> CREATE PROCEDURE createReport
> @.orderNumber int
> AS
> declare @.lineDesc01 varchar(8000)
> declare @.lineDesc02 varchar(8000)
> declare @.lineDesc03 varchar(8000)
> set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
> <td > ''+ cast(b.orderID as varchar) +''</td> bla...'
> set @.lineDesc02 = '</td>
> <td>''+ isnull(b.myDesc, '') +''</td>
> </tr>
> </table><br> bla.bla...
> set @.lineDesc03 = '</td>
> <td>como esta</td>
> </tr>
> </table></body></html>''
> FROM orders b
> WHERE b.orderID = @.orderNumber'
> exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
> How can i fix this problem?
> Is other way to do this?
> Tks in advance.
> JFB
>|||How about learning something new and useful?
Keywords: SQLXML, XSL.
If it really needs to be done straight from the server.
ML|||Tks...You right I will try to use my normal way.
Can you tell me about other ways?
Rgds
JFB
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
> Why are you creating the presentation (HTML) in the stored procedure? You
> should always try to minimize the amount of data you pass server <->
> client and you should leave as much of the presenation as you can where it
> belongs, at the presentation layer.
> But aside from that, why are you using variables at all?
> SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
> '</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
> '</td><td>como esta...up to about 8000'
> FROM orders
> WHERE orderID = @.orderNumber
> Now the presentation tier just has to write out however many columns you
> end up with (and I only suggest using columns because you need to
> concatenate).
> There are much, much, much better was to do this, even if you are going to
> insist on the bad practice of missing data operations with presentation
> details...
>
> "JFB" <help@.jfb.com> wrote in message
> news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
>|||JFB,
Generally you would return only data from the database, usually in the form
of a recordset. For example, your stored proc would be like this (contents
only):
SELECT OrderID, [Description]
FROM dbo.Orders
WHERE OrderID = @.OrderId
--
Then say you were presenting the report via an ASP web page, it would look
something like this:
// This code would be in your ASP file
// Assume command has been executed and returned a recordset "rs"
for (; !rs.EOF; rs.MoveNext())
{
Response.Write( "<tr><td>" );
Response.Write( rs.Fields("OrderID").Value );
Response.Write( "</td><td>" );
Response.Write( rs.Fields("Description").Value );
Response.Write( "</td></tr>" );
}
"JFB" wrote:

> Tks...You right I will try to use my normal way.
> Can you tell me about other ways?
> Rgds
> JFB
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>|||mmm... do you have any exmples? or any web sites to check it out?
Tks for you reply
JFB
"ML" <ML@.discussions.microsoft.com> wrote in message
news:44494029-B841-4A9E-8005-9CDDA3557DDD@.microsoft.com...
> How about learning something new and useful?
> Keywords: SQLXML, XSL.
> If it really needs to be done straight from the server.
>
> ML|||> any web sites to check it out?
www.sqlxml.org

Wednesday, March 28, 2012

long running report can not be rendered in Report Manager or through Subscription

long running report can not be rendered in Report Manager or through Subscription

long running report can not be rendered in Report Manager or through Subscription

Long Running Queries in Procedure

Long running DTS package

Monday, March 26, 2012

Long Oracle report

long html string

sql login permissions

Blog Archive

About Me