Fix: Cannot use the special principal ‘sa’. Microsoft SQL Server, Error: 15405

When importing a database in your SQL instance you would find yourself with Cannot use the special principal 'sa'. Microsoft SQL Server, Error: 15405 popping out when setting the sa user as the DBO of the database. To fix this,

Open SQL Management Studio and Click New Query. Type:

USE mydatabase
 exec sp_changedbowner 'sa', 'true'

How to Download All Your SSRS Report Definitions (RDL files) Using PowerShell

Here’s a short PowerShell script that :
1. Connects to your report server
2. Creates the same folder structure you have in your Report Server
3. Download all the SSRS Report Definition (RDL) files into their respective folders

In addition to backing up your Source Project, your ReportServer database, or good old RSScripter (see http://sqlserver-indo.org/blogs/mca/archive/2009/03/08/extract-and-transfer-rdl-files-from-ssrs.aspx) this is just another way you can “backup” or archive your reports.

#note this is tested on PowerShell v2 and SSRS 2008 R2 [void][System.Reflection.Assembly]::LoadWithPartialName("System.Xml.XmlDocument"); [void][System.Reflection.Assembly]::LoadWithPartialName("System.IO");   $ReportServerUri = "http://yourreportserver/ReportServer/ReportService2005.asmx"; $Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential ;   #check out all members of $Proxy #$Proxy | Get-Member #http://msdn.microsoft.com/en-us/library/aa225878(v=SQL.80).aspx   #second parameter means recursive $items = $Proxy.ListChildren("/", $true) | ` select Type, Path, ID, Name | ` Where-Object {$_.type -eq "Report"};   #create a new folder where we will save the files #PowerShell datetime format codes http://technet.microsoft.com/en-us/library/ee692801.aspx   #create a timestamped folder, format similar to 2011-Mar-28-0850PM $folderName = Get-Date -format "yyyy-MMM-dd-hhmmtt"; $fullFolderName = "C:\Temp\" + $folderName; [System.IO.Directory]::CreateDirectory($fullFolderName) | out-null   foreach($item in $items) { #need to figure out if it has a folder name $subfolderName = split-path $item.Path; $reportName = split-path $item.Path -Leaf; $fullSubfolderName = $fullFolderName + $subfolderName; if(-not(Test-Path $fullSubfolderName)) { #note this will create the full folder hierarchy [System.IO.Directory]::CreateDirectory($fullSubfolderName) | out-null }   $rdlFile = New-Object System.Xml.XmlDocument; [byte[]] $reportDefinition = $null; $reportDefinition = $Proxy.GetReportDefinition($item.Path);   #note here we're forcing the actual definition to be #stored as a byte array #if you take out the @() from the MemoryStream constructor, you'll #get an error [System.IO.MemoryStream] $memStream = New-Object System.IO.MemoryStream(@(,$reportDefinition)); $rdlFile.Load($memStream);   $fullReportFileName = $fullSubfolderName + "\" + $item.Name + ".rdl"; #Write-Host $fullReportFileName; $rdlFile.Save( $fullReportFileName);

Windows Azure Reporting missing .dll

I deployed my project successfully to Windows Azure, but got a surprise when I ran reports, they gave error mentioning a missing assembly Microsoft.ReportViewer.ProcessingObjectModel.dll

now I new how to add references to Microsoft.ReportViewer.Common.dll and Microsoft.ReportViewer.WebForms.dll but I couldn't find the missing one anywhere, but with a help from a colleague here is the workaround.

how did I get the third assembly, Microsoft.ReportViewer.ProcessingObjectModel.dll?

Apparently this assembly was found only in the GAC. Here is how you copy the file in the GAC :

1. Open command prompt (run as Adminsitrator)
2. cd C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.ProcessingObjectModel
3. do dir.
4. You see either one or both of the following folder:
   8.0.0.0__b03f5f7f11d50a3a
   9.0.0.0__b03f5f7f11d50a3a
   10.0.0.0__b03f5f7f11d50a3a
5. cd to one of them, and do dir.
6. You should see the Microsoft.ReportViewer.ProcessingObjectModel.dll assembly.
7. You can perform copy operation to your preferred destination folder.

SSRS 2008 Tablix control Repeat Column Headers does not work

If you faced the issue which I have faced, that you created your nice looking report, and next thing you realise the Column headers do not repeat, no matter what you did was not good enough to fix it.

Here is the solution:

Select the Tablix that you want to repeat column headers for by clicking on it.

At the bottom of the screen, find the "Row Groups" and "Column Groups" section.

Click the small drop-down-arrow on the right side of that section, and select the Advanced Mode.

Now you'll see additional lines called (static) in rows and columns groups.

In the "Row Groups" section, locate the top-outermost "static" row and click on it.

In the properties pane, you need to set the following properties:

KeepTogether = True
KeepWithGroup = After
RepeatOnNewPage = True

All of these properties must be set for this to work properly.

Good luck!

SSRS 2008 Report String Filter with LIKE operator

goto the Filters in Tablix or Group,
Add the filter,
select Expression as your Fieldname to be filtered on,
select operator LIKE
in value when you click fx button it will show you options in new window
click parameters and select the parameter you created in the report.
you'll see =Parameters!ParameterName.Value
change that to = "*" & Parameters!ParameterName.Value & "*"