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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    $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]
        $tablevalue
    }

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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)
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = $sql
$rdr = $cmd.ExecuteReader()
$dt = New-Object System.Data.DataTable
$dt.Load($rdr)

# display results
$dt

    \_(ツ)_/

    Wednesday, May 9, 2018 8:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    $dt is a data table and not an integer. 

    \_(ツ)_/

    Thursday, May 10, 2018 9:06 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Is it possible to convert it into an integer or array
    Thursday, May 10, 2018 9:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hey i have tried some different method but its not working can you please help
    Tuesday, May 15, 2018 5:08 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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