locked
Querying Variable which contains employee ids in sql query via powershell RRS feed

  • Question

  • Hi All,

    I am trying to query variable which contains multiple employee numbers in a sql query. But the output which I am getting is for only user.

    It seems that for each loop is not working. Below is my code.

    $data =Import-Csv"D:\test\employeeIDs.csv" this file contains header named as emplid.

    foreach($d in $data.emplid){

    Write-Host $d

    $SQLServer = "MIM"

    $SQLDBName = "MIM1"

    $SqlQuery = "select employee,accountname,email,location from DB1 where employeeID = '$d'"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlCmd.CommandText = $SqlQuery

    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet

    $SqlAdapter.Fill($DataSet)

    foreach ($row in $DataSet.Tables[0].rows)

    {

    $employee =$row[0].ToString().Trim()

    $accountname =$row[1].ToString().Trim()

    $email = $row[2].ToString().Trim()

    $location =$row[3].ToString().Trim()

    $DataSet.Tables[0] | Export-Csv "D:\test\Data1.csv" -NoTypeInformation 

     }

    }

    Would appreciate if i can get some help on this.

    Thanks in Advance.

    • Moved by Bill_Stewart Tuesday, December 11, 2018 8:59 PM This is not "debug/fix/rewrite my script for me" forum
    Tuesday, July 17, 2018 4:23 AM

All replies

  • Please don't post colorized code.  It cannot be copied and it is unreadable in many browsers.  Edit your post and use the code posting tool on the edit toolbar - "<>"


    \_(ツ)_/

    Tuesday, July 17, 2018 5:04 AM
  • Here is the correct way to do this.  It will generate a table of all values returned.

    $connStr = 'Server=MIM;Database=MIM1;Integrated Security = True'
    $conn = New-Object  System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $dt = New-Object  System.Data.DataTable
    
    Import-Csv D:\test\employeeIDs.csv |
        ForEach-Object{
            Write-Host $_.EmployeeID
            $cmd.CommandText = "select employee,accountname,email,location from DB1 where employeeID = '$($_.EmployeeID)'"
            $rdr = $cmd.ExecuteReader()
            $dt.Load($rdr,'PreserverChanges')
    $rdr.Close() } $dt | Export-Csv D:\test\Data1.csv -NoTypeInformation $conn.Close()


    \_(ツ)_/


    • Edited by jrv Tuesday, July 17, 2018 5:25 AM
    Tuesday, July 17, 2018 5:24 AM