PowerShell findings

I don’t have much experience with PowerShell so I’m both easy to confuse and easy to please. Let’s start with the confusing part.

The problem(s)

The idea was to change the datasource of SQL Server Reporting Services report definition (.rdl) files using PowerShell to avoid manual labor during deployment. Report definitions are just XML files so that shouldn’t be too hard.

Seeing that .rdl files use a default namespace I tried something like this:

function UpdateDatasource($newConnectionString, $reportFilePathAndName)
  # This will not work!
  [xml]$xml = Get-Content $reportFilePathAndName
  $ns = @{'' = 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'} 
  $xpath = '/Report/DataSources/DataSource/ConnectionProperties/ConnectString'
  $connectStringElement = $xml.SelectSingleNode($xpath, $ns)

But PowerShell complained that it couldn’t find an overload for SelectSingleNode() taking two parameters:

SelectSingleNode : Cannot find an overload for "SelectSingleNode" and the argument count: "2".

PowerShell findings

Thinking PowerShell had somehow loaded a dumbed down version of System.Xml.dll I found that [appdomain]::currentdomain.GetAssemblies() will happily show you all the .NET assemblies it has loaded:

PS C:\Users\Administrator\Documents> [appdomain]::currentdomain.GetAssemblies()

GAC    Version        Location                                                                                                                                
---    -------        --------                                                                                                                                
True   v4.0.30319     C:\Windows\Microsoft.NET\Framework64\v4.0.30319\mscorlib.dll                                                                            
False  v2.0.50727     C:\Program Files (x86)\PowerGUI\ScriptEditor.exe                                                                                        
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll                                             
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll                                   
False  v2.0.50727     C:\Program Files (x86)\PowerGUI\Engine.dll                                                                                              
False  v2.0.50727     C:\Program Files (x86)\PowerGUI\Engine.Shell.dll                                                                                        
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll                 
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll                             
False  v2.0.50727     C:\Program Files (x86)\PowerGUI\UI.Controls.dll                                                                                         
False  v2.0.50727     C:\Program Files (x86)\PowerGUI\Configuration.dll                                                                                       
False  v2.0.50727     C:\Program Files (x86)\PowerGUI\Interfaces.dll                                                                                          
False  v2.0.50727     C:\Program Files (x86)\PowerGUI\ScriptEditor.Shared.dll                                                                                 
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml.Linq\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.Linq.dll                           
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll       

So it was using the correct dll. Then I found that piping an object to Get-Member will show you the object’s type and it’s members:

[DBG]: PS C:\Users\Administrator\Documents> $xml | Get-Member 

   TypeName: System.Xml.XmlDocument

Name                        MemberType            Definition                                                                                                  
----                        ----------            ----------                                                                                                  
ToString                    CodeMethod            static string XmlNode(psobject instance)                                                                    
AppendChild                 Method                System.Xml.XmlNode AppendChild(System.Xml.XmlNode newChild)                                                 
Clone                       Method                System.Xml.XmlNode Clone()                      
SelectNodes                 Method                System.Xml.XmlNodeList SelectNodes(string xpath), System.Xml.XmlNodeList SelectNodes(string xpath, System.Xml.XmlNamespaceManager nsmgr)               
SelectSingleNode            Method                System.Xml.XmlNode SelectSingleNode(string xpath), System.Xml.XmlNode SelectSingleNode(string xpath, System.Xml.XmlNamespaceManager nsmgr)             

So it knew the signature. Turned out that simply typing the object and a method without the parentheses will show you the method’s overloads:

[DBG]: PS C:\Users\Administrator\Documents> $xml.SelectSingleNode

System.Xml.XmlNode SelectSingleNode(string xpath)                                                                                                                                                        
System.Xml.XmlNode SelectSingleNode(string xpath, System.Xml.XmlNamespaceManager nsmgr) 

Cool finding. But still not working.

The solution(s)

Finally I found this: just type in the path to your element – no namespaces needed. How pleasant!

function UpdateDatasource($newConnectionString, $reportFilePathAndName)
  [xml]$xml = Get-Content $reportFilePathAndName
  $xml.Report.DataSources.DataSource.ConnectionProperties.ConnectString = $newConnectionString

In case you are interested in the original problems and their solutions: problem #1 was mixing two approaches, the slightly more elaborate using SelectSingleNode() and the new one using the Select-Xml commandlet. So SelectSingleNode() got a hash table as second parameter instead of an XmlNamespaceManager – resulting in a somewhat misleading error message. Problem #2 was using an empty default namespace.

Both approaches are shown below. Note that you have to declare and use an alias for a default namespace even if it’s empty in your XML file!

function UpdateDatasource($newConnectionString, $reportFilePathAndName)
  # Solution using SelectSingleNode()
  [xml]$xml = Get-Content $reportFilePathAndName
  [System.Xml.XmlNamespaceManager] $nsmgr = $xml.NameTable
  $nsMgr.AddNamespace('dns', 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
  $xPath = '/dns:Report/dns:DataSources/dns:DataSource/dns:ConnectionProperties/dns:ConnectString'
  $connectStringElement = $xml.SelectSingleNode($xPath, $nsMgr)
function UpdateDatasource($newConnectionString, $reportFilePathAndName)
  # Solution using Select-Xml commandlet
  [xml]$xml = Get-Content $reportFilePathAndName
  $ns = @{dns = 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'} 
  $xPath = '/dns:Report/dns:DataSources/dns:DataSource/dns:ConnectionProperties/dns:ConnectString'
  $connectStringElement = Select-Xml -Xml $xml -XPath $xPath -Namespace $ns

External References

This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s