When you’re working with PowerShell and SSRS, you may occasionally come across a script that works once, then just mysteriously decides not to work anymore on a second or third invocation. Or it may just not work period, even though you think the syntax is short and straightforward, and you know you’re not misspelling any syntax.

Please note I am running this on the PowerShell ISE, and PowerGUI – and tried on both PowerShell V2 and V3.

Common Task

What was driving me crazy (at some point, I promise I’m back to my sane self now) was trying to create a folder with property. The syntax is pretty straightforward, like this:

Import-Module SQLPS -DisableNameChecking;
$ReportServerUri  = "http://localhost/ReportServer/ReportService2010.asmx"
$proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -Namespace SSRS ;
$proxy
$proxy.GetType().Namespace;	#gives me SSRS
 
#create a folder
$property = New-Object  "SSRS.Property" 
$property.Name = "Description"
$property.Value = "This folder is for any HR related reports"
 
#we need a property array to pass to the CreateFolder method
$properties = New-Object "SSRS.Property[]" 1;
$properties[0] = $property;
$foldername = "HR_" + (Get-Date -format "yyyy-MMM-dd-hhmmtt");
$proxy.CreateFolder($foldername, "/", $properties);

Broken??

Should be simple, right? PowerShell says, nope, not today. You get this error:

Cannot convert argument "Properties", with value: "SSRS.Property",
for "CreateFolder" to type "SSRS.Property[]":
"Cannot convert the "SSRS.Property" value of type "SSRS.Property"
to type "SSRS.Property"." 
At C:\Users\Administrator\Scripts\SSRS\SSRS.ps1:22 char:1
+ $proxy.CreateFolder($foldername, "/", $properties);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

Uh, what? Doesn’t that sound a little – wrong? It’s complaining about casting from SSRS.Property to SSRS.Property. It’s the same thing!
Read the rest of this entry »

I just have to blog this little gem. Every now and then I would be running a PowerShell script, I’d get an error but I wouldn’t get anything useful from the default error message besides the line number.

Little Gem

cls
$error[0] | fl -force

Sample

For example, this is an error I would get from the PowerShell console or PowerShell ISE:

Exception calling "Create" with "0" argument(s): "Create failed for
JobSchedule 'Every 3rd Friday 6AM'. "
At C:\Users\Administrator\Dropbox\Packt Draft PS Scripts\Ch03\15 -
Schedule a SQL Server Job - Every 3rd Friday at 6AM.ps1:63 char:1
+ $jobsched.Create();
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException

A sample result that I would get from this little gem, which tells me *exactly* my issue, so I don’t have to dig any further or pull up Profiler. Cool!!

Exception             : System.Management.Automation.MethodInvocationException: Exception calling "Create"
with "0" argument(s): "Create failed for JobSchedule 'Every 3rd Friday 6AM'. "
---> Microsoft.SqlServer.Management.Smo.FailedOperationException:
Create failed for JobSchedule 'Every 3rd Friday 6AM'.
---> Microsoft.SqlServer.Management.Common.ExecutionFailureException:
An exception occurred while executing a Transact-SQL statement or batch. 
---> System.Data.SqlClient.SqlException: The schedule for this job is invalid (reason: @freq_recurrence_factor must be at least 1.). 
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean
 catchException)
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteScalar(String sqlCommand)
    --- End of inner exception stack trace ---
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteScalar(String sqlCommand)
    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteScalar(StringCollection sqlCommands)
    at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteScalar(StringCollection query)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
    --- End of inner exception stack trace ---
    at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
    at CallSite.Target(Closure , CallSite , Object )
    --- End of inner exception stack trace ---
    at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, String methodName, Int32 numArgs)
    at CallSite.Target(Closure , CallSite , Object )
    at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
    at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame)
    at System.Management.Automation.Interpreter.Interpreter.Run(InterpretedFrame frame)
TargetObject          :
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
ErrorDetails          :
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at , C:\Users\Administrator\Dropbox\Packt Draft PS Scripts\Ch03\15 - Schedule a SQL Server Job - Every 3rd Friday at 6AM.ps1: line 63
PipelineIterationInfo : {}
PSMessageDetails      : 

I wanted (needed!) to write a script to automatically generate a number of reports based on sets of parameters. Most of the scripting I’ve done before are for straight up parameters that I can pass using SMO or through the URL string.

This time, I had a cascading query-based parameter, ie one parameter that is filled out based on another parameter. I was doing my usual drill with PowerShell when I got the following infamous error:

“This report requires a default or user-defined value for the report parameter. To run or subscribe to this report, you must provide a parameter value.”

Which is weird because I did provide all the required parameters. A quick search leads me to a few blog posts, most of which mentioned that it’s because of my query based parameter. Most also suggest the resolution is to convert my parameter which has query-based parameter to be non-query based.

The only problem is I can’t. And I don’t want to. That’s not a resolution. This report is meant to be used both by users for ad hoc purposes, and for massive downloading for month-end reports. Removing the query-based values for a parameter *IS NOT* a resolution. Not for me anyway.

Read the rest of this entry »

Here’s a short PowerShell script that :
1. Connects to your report server
2. Loops through a set of parameters stored in an array
3. Saves the PDF version of the report to a local folder, with appropriate names

Here’s a snippet of code to set your SSRS parameters programmatically using PowerShell

  $params = $null;
 
  #set parameters
  #here's a sample usage http://msdn.microsoft.com/en-us/library/microsoft.reporting.winforms.reportparameterinfo(v=vs.80).aspx
  $params = new-object 'Microsoft.Reporting.WinForms.ReportParameter[]' 3
  $params[0] = new-Object Microsoft.Reporting.WinForms.ReportParameter("FISCALYEAR", $fiscalyear, $false);
  $params[1] = new-Object Microsoft.Reporting.WinForms.ReportParameter("MONTHENDDATE", $monthenddate, $false);
  $params[2] = new-Object Microsoft.Reporting.WinForms.ReportParameter("SALESGROUP", $salesgroup, $false);
 
  $rv.ServerReport.SetParameters($params);

Of course this is just a sample, you can definitely extend this by using SMO to automatically pull parameter values, or use values stored in a file (among a million other things you can do with ubercool PowerShell)

Read the rest of this entry »

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.
Read the rest of this entry »

Just a short tidbit on how to list your ReportServer Items using PowerShell

#note this script is tested on PowerShell v2 and SSRS 2008 R2
 
$ReportServerUri = "http://yourserver/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
 
$items = $Proxy.ListChildren("/", $true);
 
$items | select Type, Path, ID, Name | sort-object Type, Name

Read the rest of this entry »

I just want to give a BIG SHOUT OUT to everyone who organized, presented, participated and attended VANSPUG‘s SharePoint Saturday in Vancouver (#SPSVAN). This is honestly one of the most organized, professional community I’ve attended in the last little while. All the sessions I attended were excellent, and yes – you do get that warm touchy-feely feeling after the event that kind of feels like you attended some kind of reunion (saw and talked to @jasonklatt too – we went to BCIT CST in the same batch! Although we were not in the same set, we were in the same labs).

Thank you to our presenters, including Todd Klindt (@toddklindt), Sean McDonough (@spmcdonough), Cathy Dew (@catpaint1) and Yaroslav Pentsarskyy (@spentsarsky) who devoted time and energy in coming to Vancouver, and presenting excellent materials to the inquisitive Vancouver techie community.

I know it’s been a lot of work for everyone who made this happen, especially the event chairs Sherman Woo (@SPSherm) and Michal Pisarek (@MichalPisarek). But I can’t say enough that it’s been a GREAT event. They gave out lots of cool prizes:
- Telerik Ultimate Suite Licenses
- Critical Path Training voucher
- Amazon Kindles
- Dell LCD Monitors
- Microsoft keyboard/mouse
- Bamboo Store Certificate
- KWizcom Store Certificates
- Awesome SharePoint books – including Todd Klindt’s Wrox SharePoint Administration 2010 book, Sean McDonough’s SharePoint 2010 Disaster Recovery book, and Yaroslav Pentsarskyy’s (@spentsarsky) Top 60 Custom Solutions built on Microsoft SharePoint Server 2010 . … (yes, yes, all awesome books but Lady Luck was not with me today so I didn’t win any …)

Generous sponsors include AvePoint, Habanero Consulting Group, K2, Quest Software, WPCG, Colligo Networks, Idera – and in our own little way – Black Ninja Software. Shereen (blog | twitter) and I would have wanted to present as well, but sometimes 24 hours is just not enough in a day

As someone who is part of the community – thank you again – for making this happen. And I just want to reiterate – this is an excellent community event – to share knowledge and experiences – and I look forward to more in the future. I look forward to being more active myself in the future.

And if you happen to be reading my blog, let’s continue supporting VANSPUG. It’s a great community initiative.

—

Here’s just a few points worth noting in the sessions I attended:

Upgrading SharePoint 2007 to SharePoint 2010 by Todd Klindt

- Never have your only resume on SharePoint
- When upgrading, key type should be the same. Ie, Trial 2007 to Trial 2010, or Enterprise 2007 to Enterprise 2010
- Some goofiness even after visual upgrade, for example, broken breadcrumbs
- MySite upgrade is a whole different story. The MySite site collection is upgraded – but the MySite Host is not. Also, links in mysites do get lost ..
- (eherm) .. DBAs are sometimes not gracious hosts, so you need to plan when your DB detach/attach is going to happen.
- DB attach with AAM redirect – option for Terabyte databases/big sites and site collections
- PowerShell, baby! — when you’re upgrading multiple sites.

Saving SharePoint by Sean McDonough

- RPO (Recovery Point Objective), RTO (Recovery Time Objective), RLO (Recovery Level Objective)
- At the end of the day, the questions to ask are what does your business need to be back in the event of the disaster? What is the money you stand to lose if your system is down x amount of hours?
- Content is king. Protect your king.
- When applying Service Packs, patches and hotfixes, do a Central Admin backup before and after the patch.
- Your site collections can be big – terabytes big – SharePoint can handle that, but backup becomes the boundary. You just need to plan your backup and recovery. How long will it take for you to backup the whole thing?
- For big site collections, worth looking at Remote Blob Storage solutions.
- Be careful with VM snapshots and cloning. Always test, test, test. Consistency is key.
- SharePoint 2010 is mirroring aware.
- There is a PowerShell script that can help you document your SharePoint sites. It runs for a long time, but it’s worth it. Here’s the link (http://technet.microsoft.com/en-us/library/ff645391.aspx).
- Have confidence in your solution. And confidence is gained through testing.
- If you need to back up on the cloud, check RackSpace.

Making PowerShell Less Scary for the SharePoint Administrator by Todd Klindt

(I love PowerShell. It rocketh. To the nth degree. (Ok, ok, I know I sound very geeky. I’ll stop))
- Task driven learning is an effective way to learn PowerShell. Have a task at hand. See how that can be done in PowerShell.
- Your friends are Get-Help, Get-Command, Get-Member
- When doing EnumWebs, and you want to see all, use -LIMIT ALL
- Make sure you dispose any SP objects properly, use Start-SPAssignment -Global, then your code, then Stop-SPAssignment
- Few tasks you can do with SP and Powershell:
– Activate features across site collections
– Provisioning of Managed Accounts
– Create new sites
– Backup all site Collections

I would like to say a huge and warm thank you to all who attended my sessions for SQLSaturday#43 in Redmond. It was such a great event, and I hope you enjoyed the sessions – and enjoyed the whole day with all the wonderful speakers.

As promised here are the files for my sessions:
Read the rest of this entry »