MSBI Tutor

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...

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...

USE ReportServer GO DECLARE @count INT SELECT     Cat.[Name],     Rep.[ScheduleId],     Own.UserName,     ISNULL(REPLACE(Sub.[Description],'send e-mail to ',''),' ') AS Recipients,     Sub.[LastStatus],     Cat.[Path],     Sub.[LastRunTime] INTO     #tFailedSubs FROM     dbo.[Subscriptions] Sub with (NOLOCK) INNER JOIN     dbo.[Catalog] Cat with (NOLOCK) on Sub.[Report_OID] = Cat.[ItemID] INNER JOIN     dbo.[ReportSchedule] Rep with (NOLOCK) ON (cat.[ItemID] = Rep.[ReportID] and Sub.[SubscriptionID] =Rep.[SubscriptionID]) INNER JOIN     dbo.[Users] Own with (NOLOCK) on Sub.[OwnerID] = Own.[UserID] WHERE Sub.[LastStatus] NOT LIKE '%was written%' --File Share subscription AND Sub.[LastStatus] NOT LIKE '%pending%' --Subscription in progress. No result yet AND Sub.[LastStatus] NOT LIKE '%mail sent%' --Mail sent successfully. AND Sub...

Ø   Avoid copying from remote server window to local machine, SSIS package can get corrupted. Copy the SSIS Package from shared path location to local machine.   Ø   Maintain documentation and keep it updated for Column Mapping including Data Length and Data Type for Source and Destination   Ø   Use of handshaking table between Upstream-> Datamart (MS BI) -> Downstream applications   Ø   Use of SEND MAIL NOTIFICATION TASK in SSIS Package to inform about the Data loading/ cube processing start and finish to all concerned parties   Ø   Optimize the Query/ SP run time before placing/calling it in SSIS Package to improve performance   Ø   Use of intermediary table in case of huge data loads will ensure faster load and unaffected performance of target table   Ø   Use Sequence Container with TransactionOption property set as required in order to rollback a series of task if one task fails. A...

Ø   Optimize the query to avoid unnecessary burden on the database. Avoid the use of "SELECT *" statement and specify only the columns that are required in report. Ø   Do not use Report Parameters to filter the report data unless you are using Snapshot reports. Use Query parameters instead of Report parameters as Query parameters filters data while extracting it from database server to Report Server. On the other hand, when Report parameters are implemented, report is re-executed again based on report parameters/filters and the report server filters data again based on Report parameters. Ø   Prefer to use Custom Assemblies rather than Embedded Code in case the same Custom Function (Embedded Code) needs to be referenced in multiple reports.  

SQL Server Version History Information  

CREATE TABLE #enum_job   (     Job_ID uniqueidentifier,     Last_Run_Date          INT,     Last_Run_Time          INT,     Next_Run_Date          INT,     Next_Run_Time          INT,     Next_Run_Schedule_ID   INT,     Requested_To_Run       INT,     Request_Source         INT,     Request_Source_ID      VARCHAR(100),     Running                INT,     Current_Step           INT,     Current_Retry_Attempt INT,...

DECLARE @name VARCHAR(50) -- database name   DECLARE @path VARCHAR(256) -- path for backup files   DECLARE @fileName VARCHAR(256) -- filename for backup   DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory SET @path = 'C:\Backup\' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR READ_ONLY FOR   SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')   -- exclude these databases OPEN db_cursor    FETCH NEXT FROM db_cursor INTO @name    WHILE @@FETCH_STATUS = 0    BEGIN       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'      BACKUP DATABASE @name TO DISK = @fileName      FETCH NEXT FROM db_cursor INTO @name    END    CLOSE db_cursor    DEALLOCATE db_cur...

Use msdb GO SELECT distinct j.Name as "Job Name", --j.job_id, CASE j.enabled WHEN 1 THEN 'Enable' WHEN 0 THEN 'Disable' END as "Job Status", jh.run_date as [Last_Run_Date(YY-MM-DD)], CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Successful' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' END as Job_Execution_Status FROM sysJobHistory jh, sysJobs j WHERE j.job_id = jh.job_id and jh.run_date =   (select max(hi.run_date) FROM sysJobHistory hi WHERE jh.job_id = hi.job_id )-- to get latest date