SSRS Query execution failed for dataset

January 29, 2014

When you get this error. Scratch your head !

clip_image002

In the CAML definition I saw the usage of parameters. So far so good.

clip_image004

But the Query did not run ? Sad smile

Solution :

Remove the parameter definition in the CAML.

clip_image006

This will empty the Query Parameter Value Settings

clip_image008

Add the Parameters as FILTERS

clip_image010

Now the report runs without any problems !


Oracle DB connection for SharePoint and SSRS

September 5, 2013

This is a series of adventures to took me a week to go through and find all the pitfalls !

Background : SharePoint 2010 Farm running on a Front-end IIS and Back-end MS SQL / SSRS Windows 2008 server.

I had the idea of quickly connecting the SSRS reports using Report Builder 3.0 to an out of domain Oracle DB.

And once the report was done to publish is on SharePoint. Because I had the SSRS running in SharePoint Integrated Mode. 

Since SSRS can connect to MS SQL and Oracle data source, I thought it would be nice to install the Oracle client and settings as well.

This is where the adventure starts ! Smile

Ok. First create a Free registration account on the Oracle web site in order to download the Client libraries.

http://www.oracle.com/technetwork/topics/winsoft-085727.html

image

I had already setup the Oracle INSTANT Client on my Windows 7 x64 bit laptop. So I though this is a piece of cake to do the same on the hosted server. Which was out of my domain being admin ?

Next Question that came to me is should I download the x32 bit or the x64 bit or both ?

Followed by should I install it on the Front-end server or the Back-end server or both ?

Damn so many questions unanswered ?!! I looked all over the place on the internet to no reveal. Sad smile

So let’s do it the hard way then, using trail and error method.

These are the links to the posts on how to solve all issues I encoutered, installing Oracle ODBC connections for SharePoint and SSRS :

How to install the Oracle Instant Client x32 and x64 bit on the same machine side by side.

How to solve the error 206 when installing the Oracle ODBC drivers.

How to solve Publish Reports via Report Builder 3.0 not able to run against the Oracle Data Source.

How to solve Report Builder Parameter Report not working against an Oracle Data Source ‘@’ versus ‘:’

How to Solve the Report Builder Query does not run when published in SharePoint : ORA-03134

SharePoint SSRS UNICODE issue : Solve Chinese Control Characters


MS SQL Performance Dashboard

December 6, 2012

Microsoft is distributing a nice – MS SQL Performance Dashboard which you can use in the SSMS and SSRS.

MS SQL 2005 : http://www.microsoft.com/en-us/download/details.aspx?id=22602

MS SQL 2008 / 2012 : http://www.microsoft.com/en-us/download/details.aspx?id=29063

Getting Started with the Performance Dashboard Reports in SSMS

1. Each SQL Server instance you plan to monitor must contain the procedures and functions used by the queries in the reports. Using SQL Server Management Studio (SSMS), open the Setup.sql Script from your installation directory (default of %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard) and run the script.

Close the query window once it completes.

2. In the Object Explorer pane in SSMS, right mouse click on the SQL Server instance node, then choose Reports-Custom Reports. Browse to the installation directory and open the performance_dashboard_main.rdl file. Explore the health of your server by clicking on the various charts and hyperlinks in the report.

All of the remaining reports are accessed as drill through operations from the main page or one of its children. For a detailed explanation of all installation requirements and guidance on how to use the reports, please see the help file, PerformanceDashboardHelp.chm.

Once installed you can also use it inside of the SSMS – Browse to %ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard folder and open the performance_dashboard_main.rdl report.

clip_image002

Getting Started with the Performance Dashboard Reports in SSRS

To get it to work you need to run the SETUP.SQL query in the Master DB. Copy all files from the install directory to your SSRS reports directory. And upload them to the SSRS Reporting Portal.

clip_image004

Create a Shared Data Source in the Report Server !

clip_image006

clip_image008

Once the reports are using the correct data source you can run the main Report to test it.

clip_image010

clip_image012

clip_image014

SharePoint Database Health Dashboard – Bonus

If you are running SSRS in SharePoint Integrated Mode, than you have a nice interface to monitor your database Health.

Just publish the SSRS report to a SharePoint library

image

And there you go all the data is available per content database.

image

Enjoy !!


MS Reporting Services – Recycle times

December 4, 2012

When accessing the SSRS web page the first time in the morning, this can be frustrating that the first users have to wait a long time before they can see the reporting service home page.

Many posts have been logged on the internet about this but there is descend solution except these workarounds

Step 1

Change the <RecycleTime>1440</RecycleTime> in the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer/rsreportserver.config file.

ReportSeverConfig 

Don’t forget to restart the reporting service after applying the change.

If this does not help and it won’t ! You will have to move to the final workaround.

Step 2

Make a Powershell script that will restart the service and trigger the reporting service home page.

Stop-Service "SQL Server Reporting Services (MSSQLSERVER)"
Start-Service "SQL Server Reporting Services (MSSQLSERVER)"

$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString("http://srvplsk02/Reports/Pages/Folder.aspx")

This will do the job, but again not in our case ! Sad smile

We noticed that even running this script daily at 2 AM it would work throughout the morning for about 8 hours and then again the long wainting time came along.

So a second script was needed to mimic the user activity, which I scheduled every 2 hours.

$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString("
http://yourserver/Reports/Pages/Folder.aspx")

Finally everything ran fine so far !! I Can’t understand why this most frustrating event was not fixed by MS in the first place.