MSBI Tutor

 --Transact-SQL to list retrieval, processing and rendering duration times of SSRS reports executions. /* The most often complain of users is "My report is running that slowly, why?" and when you run it as admin, it works normal. With this Transact-SQL script you can check the execution durations of report splitted in data retrieval, processing and rendering durations to analyse which part takes to most time and if it's really slow. Please remark: Querying the ReportServer database directly is not a supported way. Works with SQL Server 2005 / Reporting Services 2005 and higher version in all editions. Requires select permissions on the ReportServer database. */ -- List processing duration times of SSRS reports executions. DECLARE @begin datetime, @end datetime, @minDuration int; -- Include executions of the last month. SET @begin = DATEADD(m, -1, GETDATE()); SET @end   = GETDATE(); SET @minDuration = 3000;  -- min Duration in Millisecondes = 3 sec. ;WITH

-- Transact-SQL to query datasets with command text for all SSRS reports. /* This Transact-SQL script list for all SSRS reports the used datasources and datasets with the command text. Requires select permissions on the ReportServer database. */ -- List datasets with command text for all SSRS reports ;WITH  XMLNAMESPACES      (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'              ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'       AS rd) ,DEF AS     (SELECT RPT.ReportPath            ,R.RptNode.value('(./Query/DataSourceName)[1]', 'nvarchar(425)') AS DataSourceName            ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)')))                     ,'&gt;', '>')                     ,'&lt;', '<'

/*============================================= Variables: @MinHistExecutions - Minimum number of job step executions we want to consider @MinAvgSecsDuration - Threshold for minimum job step duration we care to monitor @HistoryStartDate - Start date for historical average @HistoryEndDate - End date for historical average Returns: One result set containing a list of job steps that are currently running and are running longer than two standard deviations away from their historical average. The "Min Threshold" column represents the average plus two standard deviations. note [1] - comment this line and note [2] line if you want to report on all history for job steps note [2] - comment just this line is you want to report on running and non-running job steps =============================================*/ DECLARE    @HistoryStartDate  datetime    , @HistoryEndDate  datetime    , @MinHistExecutions  int      , @MinAvgSecsDuration  int SET  @HistoryStartDate 

This issue can be resolved by adding a Grant permission to the end of the new stored procedure However, in real life scenario, where manual intervention is the cause of multiple errors; sometimes developers tend to miss out on this piece of code or most of the time they are not aware of it. The stored procedure executes perfectly well in a development environment (the developer has owner rights on the schema) but fails in QA or Production environment. Most of the time, this error is not realized until a major job fails, due to the absence of executor rights.  declare @spname varchar ( 100 ) declare @schemaname varchar ( 100 ) declare @sql varchar ( max ) Declare findSP Cursor           For            SELECT sys . objects . name , sys . schemas . name as [schema_name] FROM     sys . objects   INNER JOIN sys . schemas ON sys . objects . schema_id = sys . schemas . schema_id WHERE type = 'P'          Open findSP          Fetch Next

Informatica – PowerCenter IBM – Infosphere Information Server Oracle – Data Integrator Microsoft – SQL Server Integrated Services Talend – Talend Open Studio for Data Integration SAS – Data Integration studio SAP – BusinessObjects Data Integrator Clover ETL – CloverETL Pentaho – Pentaho Data Integration AB – Initio

Free software: BIRT Project D3.js GNU Enterprise (reporting sub-package) JasperReports KNIME LibreOffice Base OpenOffice Base Pentaho SpagoBI Commercial software: ActiveReports Actuate Corporation AnyChart BOARD Business Objects Cognos BI Crystal Reports CyberQuery DevExpress Reporting Dream Report by Ocean Data Systems GoodData icCube I-net Crystal-Clear InetSoft Information Builders' FOCUS and WebFOCUS Infragistics NetAdvantage Reporting InstantAtlas Jedox Jinfonet Software List & Label Logi Analytics m-Power MicroStrategy Navicat OBIEE Oracle Discoverer Oracle Reports Hyperion Oracle XML Publisher Plotly Proclarity Procurify QlikView Roambi RW3 Technologies SiSense Splunk SQL Server Reporting Services Stimulsoft Reports Style Report Tableau Targit TIBCO Text Control Whatagraph Windward Reports XLCubed Zoomdata

Reporting Services Error An error occurred during rendering of the report. (rrRenderingError) An error occurred during rendering of the report. The type initializer for 'MS.Utility.EventTrace' threw an exception. Requested registry access is not allowed. Because maximum number of rows that can be accommodated in an excel sheet is 65536(2^16=No. of items that can be accommodated by a short Integer. As it uses short Integer to refer to each row as a pk). The 65K row limitation is only for .xls files.  The new OOXML file format (.xlsx or .xlsm) can handle up to 1 million rows.   The Excel renderer in SSRS 2012 supports the new OOXML format. If upgrading SSRS is not an option, there are 3rd party custom renderers such as http://www.officewriter.com/officewriter-for-sql-server-reporting-services.aspx that can export from older versions of SSRS to the .xlsx format. 

Solution is to add the reports URL to local intranet. Open internet explorer as administrator and navigate to Tools –> Internet Options –> Security –> Local Intranet. Click Sites button. Add Reports URL (e.g. http://server/Reports or http://10.xx.xx.xx/Reports ) OK       OR Troubleshooting Steps: Below mentioned are the steps to resolve the above-mentioned error in SQL Server 2008 Reporting Services. 1. Click Start -> Right click Internet Explorer -> Select Run as Administrator from the drop-down list as shown in the snippet below. 2. In Internet Explorer -> Tools -> Internet Options . In the Internet Options dialog box; select Security Tab and then click on Sites button under Local Intranet as highlighted in the snippet below. 3. In Local Intranet dialog box; enter the Reports URL address as shown in the snippet below and then click Add button to add it to the websites. Click Close to exit and return to Internet Opti

SQL Server 2016 Database Engine: You can now configure multiple tempDB database files during SQL Server installation and setup. New Query Store stores query texts, execution plans, and performance metrics within the database, allowing easy monitoring and troubleshooting of performance issues. A dashboard shows which queries consumed the most time, memory or CPU resources. Temporal tables are history tables which record all data changes, complete with the date and time they occurred. New built-in JSON support in SQL Server supports JSON imports, exports, parsing and storing. New PolyBase query engine integrates SQL Server with external data in Hadoop or Azure Blob storage. You can import and export data as well as executing queries. The new Stretch Database feature lets you dynamically, securely archive data from a local SQL Server database to an Azure SQL database in the cloud. SQL Server automati