SSRS – Query execution failed for dataset

January 29, 2014

When you get this error. Scratch your head !


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


But the Query did not run ? Sad smile

Solution :

Remove the parameter definition in the CAML.


This will empty the Query Parameter Value Settings


Add the Parameters as FILTERS


Now the report runs without any problems !

Outlook – 2007 Connection Issues

January 29, 2014

I was experiencing an issue with a user having issues after migration of of exchange 2003 to exchange 2010.

When starting outlook 2007 it took 5 minutes before outlook was properly started Sad smile


Outlook was hanging all time.

After getting started on a wrong foot. I was thinking there was something wrong with his profile. After recreating from scratch it did not solve the issue ?

After my search on the net I tried several steps, untill this solved the issue.

It was an Add-on that was bugging me.

In Outlook 2007 go to Tools –> Trust Center –>



Go to Manage COM Add-ins


Disable all Add-ins. But notice that in some case you get this message


To solve it start Outlook as Administrator


I restarted and disabled all of them.

YES the problem was solved ! Smile

Now you can test by activating 1 by 1 to see which is giving you a headache.

This approach might apply also for other versions of Outlook.

MS SQL – How to Publish the Active Directory as a SharePoint List

January 26, 2014

Ever wondered how you could publish your Active Directory in a SharePoint List. It will show all entries dynamically.

Step 1

First of all you need to setup a linked server in MS SQL using the ADSL provider


-- sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 
'ADSDSOObject', 'adsdatasource'








SELECT * FROM OpenQuery (ADSI, 'select title, displayName, sAMAccountName, 
givenName, department, mail, telephoneNumber, sn, physicalDeliveryOfficeName
from ''LDAP://dc=domain,dc=local'' where objectCategory=''user''
AND objectCategory = ''Person'' AND displayName = ''*'' ')


-- Select the USERS to test the connection


Select * from ADSI_USERS

To Drop a Linked server

Sp_DropServer 'ADSI', 'droplogins'

Step 2

Set the correct security


Giving the correct user and password to connect.


Step 3

Create an External content type in SharePoint Designer using the ADSI data source.

And make sure that you change the Authentication Mode security of the Default and Client to BCD Identity


See here is the output.


And the beauty is that you can reuse these accounts as lookup Fields in other lists !

This makes sure you are always in sync with your AD.

Enjoy !

SharePoint – Managing the Security Model

January 26, 2014

SharePoint delivers a security Model that is a combination of AD and SP Users & Groups.

Which makes a granular approach for setting up security on SharePoint Objects.

But the Downside is that is get’s misty after a while when you want to control the security Farm wide 😦

Therefore I tried to introduce this simplified approach

I created in AD 4 OU’s :

– Department Groups

– Distribution Groups

– Security Groups

– User Structure


In the User Structure I have an OU per subsidiary.

And in the OU just a list of all individual users in that subsidiary.


The Security Goups are used to add AD users in specific Groups, to be used in SharePoint are for other applications  access like MS SQL access etc.


In Distribution Groups are the AD Groups used in Exchange as Email Groups per Department per subsidiary.


In Department Groups are the AD Groups per Department containing users across the subsidiaries.


Coming back to the SharePoint Security settings.

Here is an example on how you can keep an overview on the Site or Site Collection Access Permissions.

Extra benefits are that if new users come in, you just add them to the proper AD groups. And they get automatically access to the correct Sites Collections / Sites in SharePoint. Without too much interference of IT.


The Members SharePoint Group, is left empty


The Owners SharePoint Group, contain the Administrator


The Visitors SharePoint Group, Contain all AD Department Groups


And as last we added a Domain Group  domain\all in Pxx which has all Subsidiary AD users in it.

On downside adding AD Groups is that SharePoint can’t check who is in the AD Group as user immediately, depending on your Token Timeout settings. Default is 24 Hrs 😦


Also try to make special SharePoint Security Permission levels on top of the standard ones.

Like Contribute_MinDelete or Contribute_MinEdit etc.

Go to Site Actions -> Site Permissions -> Permission Levels



Tips using AD groups

Adding AD users to the AD Group are not immediately reflex in the Check Permission box ?

Solution is to set the token refresh timer to a more reasonable frequency, standard it is set at 10 Hours?

CLS if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"})
-eq $null) 
Add-PSSnapin Microsoft.SharePoint.PowerShell; 

$sts = Get-SPSecurityTokenServiceConfig 

$sts.FormsTokenLifetime = (New-TimeSpan -Minutes 60) # <- default 10 hour 

write-host $sts.FormsTokenLifetime 

$sts.WindowsTokenLifetime = (New-Timespan –Minutes 5) # <- default 10 hour 

write-host $sts.WindowsTokenLifetime 

$sts.LogonTokenCacheExpirationWindow = (New-Timespan –Minutes 2)
# <- default 10 mins 

write-host $sts.LogonTokenCacheExpirationWindow 


# Do a reset on WFE ! 

# iisreset 
[/sourcecode Language]

If the PS script does not work use the stsadmin.exe tool instead. I had to use this method instead too ?

Default is 1440 min or 24 Hrs !

“%CommonProgramFiles%\Microsoft Shared\web server extensions\14\BIN\stsadm.exe” -o getproperty -propertyname token-timeout

“%CommonProgramFiles%\Microsoft Shared\web server extensions\14\BIN\stsadm.exe” -o setproperty -propertyname token-timeout -propertyvalue 30

# Do a reset on WFE !


Second problem that may occur is that the Security Group Scope is not set correct when the AD Group was created. Don’t use Distribution Groups !

Change it from UNIVERSAL to GLOBAL


Bottom Line :

So as you can see by combining the SharePoint Group with AD Groups, you can keep in control.

Rule number one, is to limit individual access permissions as much as possible in SharePoint.

SharePoint – How to Debug XSL Variables

January 26, 2014

Sometimes you need to build some conditional tests in you SharePoint Forms using XSL.

All of that is not a problem, but degugging a variable is a different story.

<xsl:variable name=”LOOKUP_ID”><xsl:value-of select=”@Account_x003a_ID” disable-output-escaping=”no”/></xsl:variable>

<xsl:copy-of select=”$LOOKUP_ID” />

<!– display the value of the variable used in test below on the existence of CRM account for the current value—>

Using the <xsl:copy-of method is very useful to check the content of the variable and display it in a form at anytime.

Tip : look at the different syntax select=”$LOOKUP_ID” starts with a ‘$’ instead of a @

Enjoy !

SharePoint – How to add the Field Validation – Data Required

January 9, 2014

In some case when you have list fields you want to enforce that the user adds data BEFORE he save it to SharePoint.


But in what if the option is not available ?


You can simply add it by going to the List Settings


Select the Proper Content Type and relevant Field in this case Priority


And change the setting to Required (Must contain information)


Voila !

Windows – IE Browser History Recovery

January 3, 2014


Windows Vista and Windows 7 in Normal Privilege Mode look here :

%LocalAppData%\Microsoft\Internet Explorer\Recovery\Last Active

Windows Vista and Windows 7 in Elevated Administrator Privilege Mode look here :

%LocalAppData%\Microsoft\Internet Explorer\Recovery\High\Last Active

The main .dat file name look like the following:


Where GUID is Globally Unique Identifier which is different for each user and computer.

For each tabs and windows of IE opened, IE will create as many different .data files for each of them in the following name format to store the details and data of the web pages:


Where GUID is Globally Unique Identifier which is vary for each browsing session.

Tip: The “Active” folder, the sister folder of “Last Active”, is used to frequently store current state of IE for automatic crash recovery.

Easy recovery can be done using Previous Versions at any time, for OLDER versions of your IE tabs.


For more recent restores of your IE TABS just go the to

%LocalAppData%\Microsoft\Internet Explorer\Recovery\Active

And find the recover .dat you need based on the last crash date, there will be many in there.


Well is restoring a previous version not enough to complete the exercise.

Simply copying these files and starting IE doesn’t do a crash recovery.

You need to find the flag, it sets so IE knows its previously crashed.

Inside it’s various {GUID}.dat files which are in binary format. So that will be hard to read.

Solution :

IE prepends its recovery files with ‘RecoveryStore.’

And each session file is named like this ‘{1A332508-877F-11E4-9521-002713BCD33C}.dat’

So a recovery file is like this ‘RecoveryStore.{1A332508-877F-11E4-9521-002713BCD33C}.dat’


When IE is running it has a registry key matching the name here :

‘HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Recovery\Active’

The key is without the Prepend and file extension so such as ‘{1A332508-877F-11E4-9521-002713BCD33C}’

If IE closes down successful the key is deleted from the registry. Sad smile


So after re-entering the ‘RecoveryStore’ {GUID} ID  in the registry it restored what I lost  Smile


Enjoy !

SharePoint – Trigger a Report by using a URL, Parameters and More …

January 2, 2014

It seems easy to do, but when you have SSRS running in SharePoint Integrated mode it is a bit different.

This is how to get you going.

http://YourSite/sites/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/Reports/ MyReport.rdl&rp:CaseNumber=27&rv:ParamMode=Collapsed

The trick is to add a parameter using the prefix “rp:” for the parameter (After the .rdl report string, remove all the rest).

Adding the prefix “rv:” gives you the option to hide or collapse the parameter pane.

More options are explained here :

ReportViewer parameters for controlling full-page report rendering

ReportViewer Parameter Description Values
rv:HeaderArea Control how the ReportViewer web part’s header is displayed in full-page view. Full, None.

Default: Full

rv:ToolBar Control how the ReportViewer web part’s toolbar is displayed in full-page view. Full, Navigation, None.

Default: Full

rv:ParamMode Control how the ReportViewer web part’s header is displayed in full-page view. Full, Collapsed, Hidden.

Default: Full

rv:AsyncRender Control whether the report is rendered asynchronously or synchronously. true, false.
Default: true
rv:DocMapAreaWidth Control the display width in pixels of the ReportViewer web part’s Document Map area. Non-negative integer
rv:DocMapMode Control the display of the Document Map area of a report. Displayed, Collapsed, Hidden.
Default: Displayed
SQL Server Reporting Services 2008 R2 Extra Options available for this version
rv:DockToolBar Controls whether the ReportViewer web part’s toolbar is docked to the top or bottom. Top, Bottom.

Default: Top

rv:ToolBarItemsDisplayMode Controls turning of or on specific parts of the ReportViewer web part’s toolbar. This is an enumeration field eg. For no Actions menu rv:ToolBarItemsDisplayMode= 63 (or 0x3F); For all of Actions menu only rv:ToolBarItemsDisplayMode= 960 (or 0x3C0) Back= 1, (0x1)

Find= 2, (0x2)

Page Navigation=4, (0x4)

Refresh= 8, (0x8)

Zoom= 16, (0x10)

AtomFeed= 32, (0x20)

Actions Menu Print= 64, (0x40)

Actions Menu Export= 128, (0x80)

Actions Menu ReportBuilder= 256,(0x100)

Actions Menu Subscribe = 512, (0x200)

Default: –1

Example to hide Action menu items :

Add this  : &rv:ToolBarItemsDisplayMode=223


Why 223? You have to make the sum of the values of the parts you want to include in the toolbar. The values are shown in the reference table.


SSRS – Fix Chrome and Safari

January 2, 2014

If you need to run SSRS in Chrome or Safari it won’t work Sad smile

Here’s a fix.

Found here :

Go to the ReportingServices.js located here :

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js

Make a copy first, and append the the following functions.


//Fix to allow Chrome to display SSRS Reports
function pageLoad() { 
    var element = document.getElementById("ctl31_ctl09");
    if (element) 
    { = "visible";         

// Fix Safari to display SSRS Reports 

function FixSafari()
    var element = document.getElementById("ctl31_ctl10");
    if (element) 
    { = "visible";  //default overflow value

if (window.addEventListener) // W3C standard
    window.addEventListener('load', FixSafari, false); 
    // NB **not** 'onload'
else if (window.attachEvent) // Microsoft
    window.attachEvent('onload', FixSafari);

Chrome is working fine, I have no Safari to test at the moment.

SSRS – Adding Variables / Code to a Report Builder 3.0 report

January 2, 2014

To start adding variables in a report first activate the Properties Window Pane.


Next click in the blue part, just below the RULER bar.


This will open the REPORT Properties Pane. At the bottom you see the Variables section.


Using it in your report goes like this.


Result is a count down of Row Numbers.


Easy if you know where to look for ?!

And now add some easy code to your report.


Add the expression in the report.


Look at the result