MSBI Tutor

The SQL Server Reporting Services rendering extensions are managed through the configuration file " RSReportServer.config ". You can find this configuration file in the "ReportServer" folder. The complete path of the folder is C:\Program Files (x86)\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer. Your configuration folder location may be slightly different. Open the " RSReportServer.config " file in Notepad and search for the node name, you can find all available rendering extensions there. You can refer to the code for the first two rendering extensions "XML" and "NULL". <Reader> <Extension Name="XML" Type="Microsoft.ReportingServices.Rendering.DataRenderer.XmlDataReport, Microsoft.ReportingServices.DataRendering"/> <Extension Name="NULL" Type="Microsoft.ReportingServices.Rendering.NULLRenderer.NULLReport, Microsoft.ReportingServices.NULLRe

1. Fix at client browser level. 2. Fix at report server level. In this tip I will be explaining both methods, let's start with fixing the issue in the client browser. Fix the SSRS Compatibility Issue in IE 11 This has to be applied to all users' browsers. ·          Open the IE11 browser ·          Press F12 – which would open a developer tool at the bottom of the browser ·          Click "Emulation" tab ·          Change the "Document mode" to "Edge" ·          Try to access the report. You should now be able to access the report in IE11 without any issues. Fix the SSRS Compatibility Issue at the Server Level This change will reflect in all users' browsers; you don't need to make any changes for the client browser settings. ·          It is recommended to take a backup of the reportviewer.aspx file located at \Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\Pages. ·

Drop failed for Database 'xxx'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot drop database "xxx" because it is currently in use. (Microsoft SQL Server, Error: 3702) Not able to Delete my Database in SQL Server à Connect SSMS à right click on data base (which one is delete) à click on delete à check “ Close existing connections ” at the bottom of the Delete Object window

Report "[report_name]" failed to process: An error has occurred during report processing. Cannot create a connection to data source 'MainDataSource'. The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services. The problem occurs if you use Microsoft SQL Server 2008 Express Edition Reporting Services installed remotely from the SQL server hosting the product database. Solution: This problem is caused by limited functionality of SQL Server 2008 Express Edition Reporting Services. Note that Knowledge Portal operation on this edition of SQL Server 2008 is not supported; we strongly recommend that you follow the system requirements specified in the Knowledge Portal Installation Guide . To allow for remote connections to the product database located on SQL Server 2008 Express Edition, you should take the following steps: 1. Run SQL Server Configur

Now where you can find this web.config file? If using SharePoint integration Mode: It’s located on the web server and has the path C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config But you have to keep on mind if there is more than one SharePoint instance and what instance are you using to deploy the reports, of example the path can be C:\inetpub\wwwroot\wss\VirtualDirectories\EPMA\web.config If using SSRS native Mode: The web.config file will be located at your SQL server and it will have the path: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer . The solution for this issue is to add a section in your web.config file inside the <appsettings/> section <appSettings>     ...     <add key="aspnet:MaxHttpCollectionKeys" value="9999" />     ... </appSettings> That 9999 value should represent the number of parameters that you believe it will be used.

Cannot create a connection to data source 'xxx'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors                                                       OR An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'xxx'. (rsErrorOpeningConnection) Login failed for user ‘domain\\username’. The user is not associated with a trusted SQL Server connection. OR Double Hop Issue What’s a Double Hop Issue? To explain it in simple terms, when your SQL Reports make a call to the IIS Server (SQL Reports run on IIS) using the Authentication set for the Reports and then IIS attempts to make a call to SQL Server running on a different machine, using the same Authentication, it gets Authentication error with SQL Server. This is a case of a Double Hop, as it tries to hop around the credentials across differ

Option 1 to Enable Remote Errors - DB Engine 1.  Connect to the database engine in SSMS and navigate to the ReportServer database. 2.  Query the ConfigurationInfo table to get familiar with it.  Issue the following query: USE ReportServer GO UPDATE ConfigurationInfo SET Value = 'True' WHERE Name = 'EnableRemoteErrors' Option 2 to Enable Remote Errors - Report Server This option applies to SQL Server 2008 and newer. 1.  Connect to Reporting Services in SSMS. 2.  Right-click on the server node and go to Properties. 3.  On the Advanced page, set the EnableRemoteErrors property to True.  Option 3 to Enable Remote Errors - Script 1.       Create a text file and copy the following script into the file. Public Sub Main()    Dim P As New [Property]()    P.Name = "EnableRemoteErrors"    P.Value = True    Dim Properties(0) As [Property]    Properties(0) = P    Try      rs.SetSystemProperti

You can accelerate SQL Server Analysis Services cube processing by adjusting two setting, without changing data architecture or other costly approaches . 1. Use Shared Memory protocol to optimize source data retrieval >1.5 times It’s applicable when your data warehouse database is on the same machine as Analysis Services , which is almost always the case. (There are definite reasons for adhering to this architecture: this keeps production cube intact during processing; cube refreshing is done afterwards by synchronizing or backing-and-restoring.) Go to Sql Server Configuration Manager à SQL Server Network Configuration à Protocol For MSSQLSERVER à Shared Memory protocol is enabled Force shared memory use. Change data source connection string in OLAP data source properties: add lpc:. The connection string will look like this: Data Source= lpc: ; Integrated Security=SSPI; Initial Catalog= 2.Disable Flight Recorder on machine which does data processing

Right Click in "Cube" => "Process" => "Change Settings" => "Dimension Key Errors" Active "User Custom Error Configuration" Set "Ignore Errors" for this four drop down list "Key Not Found" "Duplicated Key" "Null key converted to unknown" "Null key not allowed" The problem with keys will be ignored.

This document describes that one of the workarounds is to add ";SSPI=NTLM" or ";Integrated Security=SSPI" to the connection string. I found that doing that really helped. Here are steps how to add additional parameter to the connection string for the BIDS environment: ·          In the BIDS environment select menu item "Project" and then "Properties" ·          Select "Deployment" tab ·          Change Target/Server property value from "YourServerName" to "YourServerName;SSPI=NTLM"

An error has occurred during report processing. (rsProcessingAborted)                Query execution failed for dataset ‘DataSet3’. (rsErrorExecutingCommand)                         For more information about this error navigate to the report server on the local server machine, or enable remote errors 1.) Go to the ReportServer Database 2.) Drilldown to Tables and select the dbo.ConfigurationInfo 3.) Open the table and change the EnableRemoteErrors to TRUE in the Value column and run Execute SQL. OR In Management Studio, you must be logon on instance of report server and enable the property EnableRemoteErrors to see more details that error. Connect Management Studio à in screen: Connect to Server, server type: Reporting Services à locate the server instance in Server Name; after connected, in Object Explorer, right-click in server instance à properties à advanced tab à EnableRemoteErrors set property like TRUE and restart internet browser. OR