I need some help reagrding one powershell script that help to run SQL query RRS feed

  • Question

  • $SQLServer = "DESKTOP111"
    $SQLDBName = "Employee"
    $uid ="username"
    $pwd = "password"
    $SqlQuery = "Select CustomerID From Emp Where City = 'Stavanger';"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; User ID = $uid; Password = $pwd;"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $SQLdataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($SQLdataSet) | Out-Null
    $tablevalue = @()
    foreach ($data in $SQLDataset.tables[0])
       $tablevalue = $data[0]

    Below is the SQL Query and this the output is --> "1"

    "Select CustomerID From Emp Where City = 'Stavanger';"

    And i need this out in simple variable  like $tablevalue , 

    and i getting that value from the above script but and simplification(Modification) will be helpful..

    Wednesday, May 9, 2018 7:24 PM

All replies

  • Not the correct code for what you are trying to do.  Just run the connection "CreateCommad()" then execute and fill a table object.

    Your code would be used to create an update adapter for use with databound forms.

    $sql = "Select CustomerID From Emp Where City='Stavanger'"
    $connStr = "Server=$SQLServer;Database=$SQLDBName;Integrated Security=True;User ID=$uid;Password=$pwd;"
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $sql
    $rdr = $cmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    # display results


    Wednesday, May 9, 2018 8:06 PM
  • Thank you for your reply , Now its working fine but there is one more step i need to add

    But its not working can you please check below code

    if($dt  -eq  0)
        Write-Host "Running fine"

    esleif($dt - eq 1)


        Write-Host "Running with error"


    Thursday, May 10, 2018 8:38 AM
  • $dt is a data table and not an integer. 


    Thursday, May 10, 2018 9:06 AM
  • Is it possible to convert it into an integer or array
    Thursday, May 10, 2018 9:34 AM
  • A DataTable is an array or Row objects.

    See: https://msdn.microsoft.com/en-us/library/system.data.datatable(v=vs.110).aspx


    Thursday, May 10, 2018 9:40 AM
  • Hey i have tried some different method but its not working can you please help
    Tuesday, May 15, 2018 5:08 AM
  • Hey i have tried some different method but its not working can you please help

    What is not working?


    Tuesday, May 15, 2018 6:20 AM