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: 247180

  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?

  ---

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  December 19, 2016 at 8:49 am

  #1918697

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

  ---

• 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 🙂