I have a report that runs perfectly well when scheduled, but when run on demand it seems to put a ridiculous load on the target database, sometimes to the point of virtually halting the server. Is there any reason it should behave differently when run on demand than when it's scheduled? Thanks.
I have a report which is scheduled to run every monday morning and it generates PDF and writes it to a shared location. Shared location and schedule is defined through Subscription. All this is working fine so far. Now I need to provide a facility from the webpage to execute this scheduled task on demand. So there would be a button on the website which would actually run this scheduled task and update the PDF at the shared location. How do I run this scheduled task/subscription using the vb.net code. Is there anything in ReportService2005 or ReportExecution2005 webservice? Please advise.
I have been tapped to help with fixing a reporting tool. We have a Sql Server database/crystal reports(10) setup. I havent had the chance to look at the tables in the DB yet, but I was told that aggregate tables were used. In my past experience with crystal reports, we used database views to feed crystal reports (in Oracle). I was thinking that I could somehow use views instead of tables and then try to re-index the base tables and compile satitics (if theres such a thing with Sql Server). I was also going to look into bottlenecking and locking (table locking as opposed to row or page locking for the lookup tables...to reduce overhead on the main tables) but, I'm not sure if it'll make a difference since this is just a demo server with no major traffic hitting it yet.
The question is, does anyone have any experience with crystal reports running slow with Sql Server, what should I look out for??
I have a new setup of report server. I have deployed three reports that I have also scheduled to run and save as an Excel spreadsheet to a network locations. The reports do not run and I get the error message below. Does anyone have any ideas? Anyone run into this issue before
ReportingServicesService!dbpolling!10!02/29/2008-17:29:07:: i INFO: NotificationPolling finished processing item 33d62282-2a03-483c-bd47-f41ec78c01d5 ReportingServicesService!library!4!02/29/2008-17:29:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., AuthzInitializeContextFromSid: Win32 error: 110; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. ReportingServicesService!library!4!02/29/2008-17:29:07:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties. ReportingServicesService!subscription!4!02/29/2008-17:29:07:: i INFO: Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The report server has encountered a configuration error. See the report server log files for more information. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. at Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid) at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String userName) at Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String userName, IntPtr userToken) at Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation) at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType catItemType, Byte[] secDesc, ReportOperation rptOper, String reportPath) at Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32& executionOption, String& savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime& historyOrSnapshotDate, Byte[]& secDesc) at Microsoft.ReportingServices.Library.RSService._GetReportParameters(ClientRequest session, String report, String historyID, Boolean forRendering, NameValueCollection values, DatasourceCredentialsCollection credentials) at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters) at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute() at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper() --- End of inner exception stack trace ---
I'm hoping someone here will be able to guide me in the right direction. I am trying to figure out a way of looping through table columns or a flat file in which there will be three parameters every time. With these parameters I am then wanting to run a report using two of these parameters to feed into the report and then email this out to an email address which would be my third parameter. Does anyone here have any idea how I would go about doing this?
I want to run the Reports on a system that has Oracle Database but reporting Services are not installed there. Only Application exe file will be there designed in .NET Windows Forms.
I am trying to find a way of creating my Repoting Services reports of automatically refreshing once our ETL load has completd. I have created a process that creates a file is a particular directory once the data has completed loading however I am unsure how to make the report look for this file and then refresh the report.
I'm trying to do running total of the calculated field. Here is the detail of my RDL:
In a table, I have "textbox34" with the formula =count(Fields!ADF_NO.Value) in it and "textbox30" with the formula =first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value in it. I have divided "textbox 34" with "textbox30", and placed the results in "textbox36". Now, I want to do a cummulative total (Running total) of "textbox36", using the formula =runningvalue(reportitems!textbox36.Value, sum, nothing) in "textbox14". Its giving me an error saying:
"[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox14' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."
I have created some reports using Report Builder however they autorun with default values when selected in report manager, cant think many people would want this to happen. Is there a way of getting rid of the default values in RB or preventing auto execute in RM?
This should be brief, hopefully. Every morning all our SSIS updates run on a schedule(s) by SSMS Agent Jobs. Most of the time this is acceptable, however, during the month end accounting process it would be desirable for accounts to be able to run one of the jobs on demand.
Is there a way that this can be done without having to install and use SQL Server Management Studio on account machines? The potential damage that can be done, even inadvertently, with this utility is too great to even consider giving them this package.
I'm trying to find the best way to add ability to send a SRS report by email on-demand. My users want to access a reprot via the default SRS HTML viewer and click something to intitate an email. They would then supply an email address to receice the reprot.
The subscription option doesn't work because the receipents will not be getting reports on a regular basis.
I was thinking about adding an email link on a report that called a web service sending adequate information about the reprot (report name, parameters used, etc.)
Is there any sample code to have a web service render a report and send via email?
Is there a better way to accompolish this requirement?
We are thinking to create a small scale of the "Software On-Demand" model for Support Ticket Management System which means that the client can login to do a support tick and shared the application using the same URL.
Couple options though: 1) We design by table such has a SUPPORTS table with has a foreign key of the ClientID from CLIENTS table. This will cover that client can only can a specific data within the SUPPORTS table. 2) Alternatively, create a dynamic table which is created specifically for "Client B" for instance for SUPPORT table and it will be called such as ClientBSupport table. Something that can be common it will be shared such as like look-up table.
Couple consideration though: 1) Considered client wanting to backup this table. Obviosly it has too querying based on the client id to get the right data. 2) Security withing that table. Is the data needs to be incrypted ... I guess this is expensive excercise. 3) Possibly have a model that access to this table in querying directly etc etc. 4) If the Support table is getting larger and larger and it's not fair for small client accessing the small information which has equal to the one that has big records? Cause it's sharing table.
I have a dashboard page that I would like to load fast. But I would also like the user to be able to run the report On-Demand. The report has start date and end date parameters which default to the first and last day of last month. If I use a Cache plan that loads at 6AM the report runs with the default parameters set in the report, the user has to wait until it completes before they can change the parameters.
If I uncheck "use default" parameters in the report I lose the ability to set a cache refresh plan with the default parameters because the option is greyed out. So is there anyway outside of creating a second copy of the report to allow a user to open a report, change the parameters and run it, but also have a way to schedule some sort of Cached report for the default parameters so I can put that on a WebPart page that would load instantly?
We have some reports run quite slow because the queries are complicate and tables are large. So we create a materialized view in Oracle which store the result from the query and refresh it occationally. How to do this in MSSQL? I try the indexed view but seems it has lots of restrictions, our query has sub queries and cross database table joins so can't use the indexed view. Any other object or temp table can be used to cache the report data and be accessed globally by other procedures?
Background: In my current company the business users maintain a huge quantity of master data using excel. Then a series of SSIS jobs are edited and manually executed.
Goal: the challenge is to replace this process using MDS. One of the requested features is the possibility for the users to edit or insert new master data using the Web UI or the Excel Add-in and when they are done perform a merge of the master data in the target, in this case in the reporting DB.
The perfect solution for me is something like trigger the execution of a SSIS package to export the data from the subscription views to the reporting DB after the business rules are apply to a specific entity.
Hello SQL Server Experts, Data Analysts, and Report Writers et al:
re: Reporting Options with SQL Server
I wanted to propose an offshoot to the pryor thread:
Would anyone take a stab at comparing Access Reports, Crystal Reports, Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?
I guess this is a far as capabilites, ease of use, limitations, and especially formatting or presentation of the end report product?
Thank you to all, and I hope this is a beneficial discussion to others.
I have an already published application running several MS SQL Server 2000 Reporting Services report. I need a way to find from either the reporting services log or the application server (IIS) logs or windows log to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend? How can I acheive this?
I have already got the IIS logs and it did not give the required info. I have looked into the Reporting Services logs but it does not provide the info either.
I have an already published application running several MS SQL Server 2000 Reporting Services report. I need a way to find from either the reporting services log or the application server (IIS) logs or windows log to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend? How can I acheive this?
I have already got the IIS logs and it did not give the required info. I have looked into the Reporting Services logs but it does not provide the info either.
My issue is with converting multi-value parameters:
In Crystal Reports, you can set a parameter to accept multiple vales (Discrete, Range or Discrete and Range).
As an example:
I have a database table with a column called ID. I can create a parameter called param_id and set the options of the parameter to "Allow multiple range values".
With this setup, I can limit the result set of the report by comparing the param_id parameter to the ID column in the database. Because param_id is a multi-value range parameter, I can pass it the following data: 1 - 50 60 - 80 150 - 127
This will only return results within those ranges.
Does anyone know if SSRS provides this kind of functionality?
Is there a way to copy reports from other users "My reports" folder? I am logging onto the management studio reporting services using an administrative account and I am able to view the reports from all users' "My Reports" folder. But I am not able to export them as an rdl file. I am able to export reports from other common folders, but not from the "My Reports" folder.
The reason I need to do this is some of the users have created some reports in one environment and the reports are available in their respective "My Reports" folder. I need to move these reports to their corresponding "My Reports" folder in another environment.
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Hello Reporting Services 2005 users or Reporting Services Team, I have done everything to get pass this error but no luck. My setup is :- WIndows 2003 Standared Edition SP1 Sql Server :- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1) Reporting Services starting SKU: Standard. From the Reporting Services Configuration Manager: I can see all other things configured except Encryption Keys(blue sign) Initialization(Grey sign) and Execution Acct(Yellow sign) My windows Service Identity is using : NT AuthorityNetworkService Builtin Acct :NetworkService
Web Service Identity :- ASP.NET Service Acct :- NT AuthorityNetworkService
DataBase Setup :- Credentials Type :- Service Credentials
I have also done everything from here thanks to Göran http://stevenharman.net/blog/archive/2007/03/21/Avoiding-the-401-Unauthorized-Error-when-Using-the-ReportViewer-in.aspx
and
http://support.microsoft.com/default.aspx?scid=kb;en-us;896861 and
http://forum.k2workflow.com/viewtopic.php?t=619&
If you guys any solution for this please let me know. I was wondering could this be a scale-out deployment issue ?
I also get the error when setting up the DataBase Setup : Although saving the database connection info succeeded the The report server cannot access internal info about this deployment to determine whetherthe current con fig is valid for this editionThis could be a scale-out deployment and that the feature is not supported by this editionTo continue use a diff report server database or remove the encription keys
My Error log file is below:- w3wp!webserver!5!16/05/2007-14:52:46:: i INFO: Reporting Web Server started w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. w3wp!library!5!16/05/2007-14:52:46:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file. w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Reporting Services starting SKU: Standard w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Evaluation copy: 0 days left w3wp!resourceutilities!5!16/05/2007-14:52:46:: i INFO: Running on 1 physical processors, 2 logical processors w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!runningjobs!5!16/05/2007-14:52:46:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds w3wp!library!5!05/16/2007-14:52:48:: i INFO: Call to GetPermissions:/ w3wp!library!5!05/16/2007-14:52:48:: i INFO: Catalog SQL Server Edition = Standard w3wp!library!5!05/16/2007-14:52:48:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server. w3wp!library!5!05/16/2007-14:52:48:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server. w3wp!library!5!05/16/2007-14:52:49:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server. w3wp!library!5!05/16/2007-14:52:49:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerServiceUnavailableException: The Report Server Windows service 'ReportServer' is not running. The service must be running to use Report Server.