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.
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 = $property; $foldername = "HR_" + (Get-Date -format "yyyy-MMM-dd-hhmmtt"); $proxy.CreateFolder($foldername, "/", $properties);
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!
Also, this code works in every other .NET language (modified using that language’s syntax of course) .. why won’t it work with PowerShell? I’m definite it’s not the way I’m using arrays. That would be my first culprit if I were to investigate, because it looks weird. But I know that works, I’ve used it in so many other scripts.
So what is it?
It looks like even when you use the -Namespace argument, PowerShell will still use and expect some of the autogenerated namespaces in the arguments. In reality, you may be using the “SSRS” namespace, but in the background it may be expecting the real, fully qualified autogenerated namespace name, like:
This becomes problematic, doesn’t it?
The workaround is to not use the namespace, and to use the autogenerated namespace in all the references. Here’s the modified script:
cls $ReportServerUri = "http://localhost/ReportServer/ReportService2010.asmx" #note no -Namespace argument $proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential ; #get autogenerated namespace $type = $Proxy.GetType().Namespace $datatype = ($type + '.Property') #display datatype, just for our reference $datatype #here we create a new object based on the autogenerated namespace #this would be equivalent to the following line in the original script: #$property = New-Object "SSRS.Property" $property =New-Object ($datatype); $property.Name = "Description" $property.Value = "Finance" #generate timestamped foldername $foldername = "HR_" + (Get-Date -format "yyyy-MMM-dd-hhmmtt"); #Report SSRS Properties #http://msdn.microsoft.com/en-us/library/ms152826(v=sql.90).aspx $numproperties = 1 $properties = New-Object ($datatype + '')$numproperties $properties.GetType().Name $properties = $property; $proxy.CreateFolder($foldername, "/", $properties);
There you go. Hopefully this saves you some time, and some headaches!