Run / Preview SSRS reports without Cache in Local Machine through BIDS / SSDT

In this blog, we’ll discuss how to run / preview SSRS reports without cache in Local Machine in BIDS / SSDT.

Problem Statement
We all have done work in SSRS reports. There are instances, when we made some changes in the procedures or the direct scripts or views we are using in the Report and want to see the Preview of the report in the BIDS, but the preview still showing the old data.

Reason behind this is BIDS/SSDT always run the reports first from the cache copy, which is the last run copy of the report run faster and save our time, if the report is run with same set of parameters.

When we click on the preview button for report in BIDS, first it checks whether the .rdl.data file is present for the current report in your local system. If it not present, then BIDS will automatically create it.

  1. If yes, it checks and match whether the same set of parameter(s) are use to run the report,as it is having in .rdl.data file. Then run the report from .rdl.data file,i.e., the CACHE copy of the report
  2. If no, then overwrite the existing .rdl.data file with the new set of parameters and creates a new CACHE copy of the report.

Solution A
We just need to navigate the Reports Solution Folder, go inside the folder and need to delete the files with extension .rdl.data

Solution B
To make the job easier and automated of locating all the .rdl.data files of any Report project and delete them all in one go, all you need to add a very simple configuration in the BIDS/SSDT, which I found in one the blog over the internet.

  1.  Open the BIDS/Visual Studio.
  2. Click on Tools Menu => External Tools
  3. It’ll give a new window, to add External Tool
  4. Add the below details and click on ADD button.
  5. Title: Clear Report Cache-<Project Name>
    “ To make it more clear to you which project is going to effect”   
  6. Command: %WinDir%\System32\cmd.exe
  7. Arguments: /C DEL /S /Q “D:\ReportTest\ReportTest\*.rdl.data”  “Here you need to declare the path of the Report project in your local machine. You need to be very careful while specifying the path with the proper extension. As it run a DOS command to delete those files. Once deleted, you cannot recover it. As it is like Shift+Del or Permanent Delete”
  8. Check options: Use Output window & Close on exit
  9. Click ‘OK’

The title you just specified in the window, now comes just above the External Tools option.

To clear all the cache copy of all the reports of that project, now all you need to do is click on Tools Menu => Click on the new option — Clear Report Cache-<Project Name>
On clicking the new option, you can see the at the bottom, about the deletion of all the .rdl.data files in the Output window.

Please share your valuable comments.

Resolved : How to remove SQL Error Window “Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

In this blog, we’ll discuss how to fix “Index was outside the bounds of the array” SQL error.

While trying to open / view the functions / stored procedures / views or any other SQL object of higher version of SQL Server like SQL Server 2012 or above from SQL Server 2008/ 2008 R2, we are getting a SQL Error Window, which says the below and not allow us the open the SQL objects, though the user might have the full rights in that particular database of the SQL Server.

“Index was outside the bounds of the array.(Microsoft.SqlServer.Smo)”

It is actually an exception generate while connecting to higher version of SQL Server like 2012 & above from SQL Server 2008  or SQL Server 2008 R2.

To resolved this exception, Microsoft release an patch in 2014 which, which will eliminate this exception and the user can easily connect with higher version SQL Server from SQL Server 2008 or SQL Server 2008 R2.

Please download the Sql Server 2008 Service Pack4 from the given URL

https://www.microsoft.com/en-in/download/details.aspx?id=44278

Please download the Sql Server 2008 R2 Service Pack3 from the given URL

https://www.microsoft.com/en-us/download/details.aspx?id=44271

In the download link after click on the submit button, you need to select the 32 or 64 bit as per your system.

 

Please share your valuable comments.

Resolved : Sgen.exe Error while building the Script task in SSIS or in any other .Net development in Windows 8 or above

In this blog, we’ll discuss how to remove the Sgen.exe error while building any Script task in SSIS or in any other .Net development in Windows 8 or above with the below mention steps.

1. Download & install the SDK from below URL

https://developer.microsoft.com/en-us/windows/downloads/windows-8-1-sdk

Or  ( for other version if above does not work)
https://developer.microsoft.com/en-us/windows/downloads/windows-8-sdk

Close the Visual Studio & SQL in your system and open the above URL, click on the download & install link.

2. It’ll download a set up in your system. Run that setup.
3. Select Download & install.
4. After installation, restart your PC is complete open the below path

C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools

5. Locate the Sgen.exe and copy it

6. Now paste it in the below location

C:\WINDOWS\Microsoft.NET\Framework\v3.5\

7. All done.

Please share your valuable comments.