Help needed with multiple invoke-sqlcmd commands

• chinna1990

  Ten Centuries

  Points: 1005

  More actions

  December 19, 2016 at 8:40 am

  #319858

  Hi ,

  I am using invoke-sqlcmd cmdlet to fetch the data initially then use that data to fetch other details (looping through these details)

  code:

  Import-module sqlpls

  $instance = <servername>

  $server = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instance

  $values = Invoke-Sqlcmd -Query "select id from table1 " | Out-String

  #Values variable is array of ids, below code is to loop through the numbers

  $Values |foreach-object {

  $value = $_ | out-string

  $name = Invoke-Sqlcmd -Query "select name from table1 where id='$value'" | Out-String

  write-host "name for id $value is"

  write-host $name

  }

  Here in the above code , write-host $name displaying blank space instead of name for each id.

  Please provide your suggestions to correct the code.

  Thanks in advance.

  Thanks,

  Prathap SV.

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  December 19, 2016 at 8:46 am

  #1918696

  May I ask why you don't just fetch everything you need in one go, rather than the inefficient looping technique you have coded?

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Phil Parkin

  SSC Guru

  Points: 246993

  More actions

  December 19, 2016 at 8:49 am

  #1918697

  Also, I think you need to specify -ServerInstance when using Invoke-Sqlcmd.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• chinna1990

  Ten Centuries

  Points: 1005

  More actions

  December 19, 2016 at 8:52 am

  #1918701

  Hi ,

  Thanks for the reply.

  I am new to powershell.

  I am trying to fetch the details of names based on each id. For each name , (other columns) I wanted to process other operations (like use this name to send a mail etc)

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  December 19, 2016 at 9:24 am

  #1918714

  chinna1990 (12/19/2016)

  ---

  Hi ,

  Thanks for the reply.

  I am new to powershell.

  I am trying to fetch the details of names based on each id. For each name , (other columns) I wanted to process other operations (like use this name to send a mail etc)

  I would recommend one job at a time. It is more reusable and maintainable. See Mr Parkin's comments above. He is leading you in the right direction in my opinion.

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• chinna1990

  Ten Centuries

  Points: 1005

  More actions

  December 20, 2016 at 9:29 am

  #1918958

  Thanks for the inputs, its worked!

  I have fetched entire table into one array and processed each column 🙂