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);

Check if SQL View Exist

IF OBJECT_ID ('_YOUR_VIEW_NAME', 'V') IS NOT NULL DROP VIEW _YOUR_VIEW_NAME';

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.

The report definition for report 'path' has not been specified

So I guess you hit the error like it did, reports were working fine in your local project, but when uploaded to windows azure they stopped working.

The full errormessage is:
The report definition for report 'path' has not been specified. Could not find file 'path'.

This error might occur when you try to run a local report (rdlc).

The solution is to set the property "Build Action" of the rdlc to "Content". Now your installer will copy the physical rdlc.

you have to mark all .rdlc files in your project as Content.

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 Paging shows Question mark (?)

Yes I got confused as well as to what this suddenly a new thing in SSRS 2008 and how to solve it, I even thought its a bug, but guess what, its a feature Microsoft has introduced question mark is there because report did not render all the pages and only current page worth of records were processed, it is called On Demand Report Processing.

Yes I do understand the potential of massive performance gains but lets be honest whats the point of showing Page 1 of 2? its not really helpful.

Here we go, you will have to add a little piece of code in your report, so add a textbox in your header or footer containing =Globals!TotalPages, please note that its only allowed in the header or footer, and then you'll see that report will show current number of pages.