0

I have the following table inside SQL:-

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ORG_UNIT]
      ,[Rollup_Business]
      ,[Rollup_Region]
  FROM [Flavors_DevEx].[dbo].[BU_ROLLUP]

enter image description here

and when i execute the following Power Shell to retrieve all the data :-

Function ProcessSQLServerDataBU_ROLLUP([string]$DBServer, [string]$DBName, [string]$Query)
{
 try
  {
      $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DBServer;Integrated Security=True;Initial Catalog=$DBName")                        
      $conn.Open();                        
      $query = $Query                        
      $dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);                        
      $dt = new-object System.Data.DataTable;                        
      $dap.Fill($dt);                        
          foreach($r in $dt.Rows)                        
           {                        
              Write-Host $r["ORG_UNIT"]                        
           }                        
$conn.Close();
   }
 catch
 { 
     #Write error message on screen and to a LOG file
     write-host $_.Exception.Message
     $_.Exception.Message >> "d:\error.log"
  }
  finally
  {
       $ErrorActionPreference = "Continue"
  }
} 

ProcessSQLServerDataBU_ROLLUP "(localdb)\ProjectsV13" "Flavors_DevEx" "SELECT [ORG_UNIT] FROM [BU_ROLLUP]"

i will get this result (where 4 represents the number of rows).. so how i can eliminate this number? and only show the data itself?

4
1
11
111
1111

Thanks

John John
  • 1
  • 72
  • 238
  • 501
  • [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Jan 04 '23 at 14:31
  • 1
    I think it is coming from one of the powershell statements and you need to add to end of line : | Out-Null – jdweng Jan 04 '23 at 14:37
  • @jdweng can you advice more please? – John John Jan 04 '23 at 14:38
  • 1
    Try : $dap.Fill($dt) | Out-Null The value 4 is coming from the powershell script and sending the 4 to null will solve issue. I'm not 100% sure which line is outputting the 4. – jdweng Jan 04 '23 at 14:54
  • 2
    @jdweng is correct. That is output from the [.Fill() method](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbdataadapter.fill?view=net-7.0&viewFallbackFrom=dotnet-plat-ext-7.0#system-data-common-dbdataadapter-fill(system-data-datatable)). Suppress that with `$null = $dap.Fill($dt)` or `[void]$dap.Fill($dt)` or `$dap.Fill($dt) | Out-Null` – Theo Jan 04 '23 at 14:54
  • @jdweng yes correct – John John Jan 04 '23 at 15:41
  • @Theo yes correct – John John Jan 04 '23 at 15:42
  • 1
    What happens if you put a `set nocount on;` before the statement? – Ben Thul Jan 04 '23 at 15:53
  • 1
    In short: any output - be it from a PowerShell command, an operator-based expression, or a .NET method call - that is neither captured in a variable nor redirected (sent through the pipeline or to a file) is _implicitly output_ from a script or function. To simply _discard_ such output, use `$null = ...`. If you don't discard such output, it becomes part of a script or function's "return value" (stream of output objects). See the linked duplicate for more information. – mklement0 Jan 04 '23 at 17:40

0 Answers0