Powershell results are not collected in C #

Question

When button 1 is clicked, the code below runs the PowerShell script to get the current instances of SQL Server. However, when this is done, the result set (result variable) has a score of 0 rows from PowerShell output. When I run the same code in native PowerShell, it displays 3 lines with instance names.

Can anyone advise if I missed something?

private void button1_Click(object sender, EventArgs e) { //If the logPath exists, delete the file string logPath = "Output.Log"; if (File.Exists(logPath)) { File.Delete(logPath); } string[] Servers = richTextBox1.Text.Split('\n'); //Pass each server name from the listview to the 'Server' variable foreach (string Server in Servers) { //PowerShell Script string PSScript = @" param([Parameter(Mandatory = $true, ValueFromPipeline = $true)][string] $server) Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned -Force; Import-Module SQLServer; Try { Set-Location SQLServer:\\SQL\\$server -ErrorAction Stop; Get-ChildItem | Select-Object -ExpandProperty Name; } Catch { echo 'No SQL Server Instances'; } "; //Create PowerShell Instance PowerShell psInstance = PowerShell.Create(); //Add PowerShell Script psInstance.AddScript(PSScript); //Pass the Server variable in to the $server parameter within the PS script psInstance.AddParameter("server", Server); //Execute Script Collection<PSObject> results = new Collection<PSObject>(); try { results = psInstance.Invoke(); } catch (Exception ex) { results.Add(new PSObject((Object)ex.Message)); } //Loop through each of the results in the PowerShell window foreach (PSObject result in results) { File.AppendAllText(logPath, result + Environment.NewLine); // listBox1.Items.Add(result); } psInstance.Dispose(); } } 

Answer 1

I was able to get around this problem using Win32_service instead of SQLPS.

 Param([Parameter(Mandatory = $true, ValueFromPipeline = $true)][string] $server) $localInstances = @() [array]$captions = GWMI Win32_Service -ComputerName $server | ?{$_.Name -match 'mssql *' -and $_.PathName -match 'sqlservr.exe'} | %{$_.Caption} ForEach($caption in $captions) { if ($caption -eq 'MSSQLSERVER') { $localInstances += 'MSSQLSERVER' } else { $temp = $caption | %{$_.split(' ')[-1]} | %{$_.trimStart('(')} | %{$_.trimEnd(')')} $localInstances += ""$server\$temp"" } } $localInstances; 

Answer 2

To get a possible PowerShell error, I would try sth. eg:

 private void button1_Click(object sender, EventArgs e) { //If the logPath exists, delete the file string logPath = "Output.Log"; if (File.Exists(logPath)) { File.Delete(logPath); } string[] Servers = richTextBox1.Text.Split('\n'); //Pass each server name from the listview to the 'Server' variable foreach (string Server in Servers) { //PowerShell Script string PSScript = @" param([Parameter(Mandatory = $true, ValueFromPipeline = $true)][string] $server) Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned -Force; Import-Module SQLServer; Try { Set-Location SQLServer:\\SQL\\$server -ErrorAction Stop; Get-ChildItem | Select-Object -ExpandProperty Name; } Catch { echo 'No SQL Server Instances'; } "; using (PowerShell psInstance = PowerShell.Create()) { psInstance.AddScript(PSScript); psInstance.AddParameter("server", Server); Collection<PSObject> results = psInstance.Invoke(); if (psInstance.Streams.Error.Count > 0) { foreach (var errorRecord in psInstance.Streams.Error) { MessageBox.Show(errorRecord.ToString()); } } foreach (PSObject result in results) { File.AppendAllText(logPath, result + Environment.NewLine); // listBox1.Items.Add(result); } } } } 

Answer 3

The reason it doesn't work is because psInstance.AddParameter only adds parameters to commands, it doesn't work with script. You need to find another way to get the $ server parameter in the script. Try using these two powershell examples to figure out what I mean. The first will display all processes (ignores AddParameter), and the second shows only svchost processes.

one)

 $ps = [system.management.automation.powershell]::create() $ps.AddScript("get-process") $ps.AddParameter("name","svchost") $ps.invoke() 

2)

 $ps = [system.management.automation.powershell]::create() $ps.AddCommand("get-process") $ps.AddParameter("name","svchost") $ps.invoke()