Need help: No result when querying SQL data in Powershell

Welcome Forums General PowerShell Q&A Need help: No result when querying SQL data in Powershell

This topic contains 8 replies, has 4 voices, and was last updated by

 
Participant
3 weeks, 3 days ago.

  • Author
    Posts
  • #124482

    Participant
    Points: 36
    Rank: Member

    Hi All,

    I am a newbie to Powershell and below is the scenario where I'm puling SQL data in powershell and inserting records into SQL.

    Scenario:

    In table1, we have records with series like M100 and its series like "M100-01","M100-02","M100-03","M100-04","M100-05","M100-06","M100-07","M100-08","M100-09" etc.

    Similarly for other M200, M300 etc. records.

    I need to pull up al records with are under series like M100-01","M100-02","M100-03" etc and insert new records under single name M100

    Below is the logic I used but output does not show any results though DB has values.

     

    $items = @("M100","M200","M300","M400","M500","M600")
    foreach($item in $items)
    {

    Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Delete from  where MetricID = (Select _ID From where Name = '$item')" -username $dbUsername -password $dbPassword

    $findingsinv=Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Insert table1(MetricID,Resource,Type,Details,LastModified)
    Select (Select _ID From table2 where Name = '$item') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d
    Join table 2 c on c._id = d.metricid
    where Name like '$item%'" -username $dbUsername -password $dbPassword

    }

    I doubt this condition of Name like '$item%'" is not taking the series. I tried multiple ways of giving to pull all series records but just does not work. Can someone assist here?

    Thanks!

     

     

  • #124526

    Participant
    Points: 190
    Helping Hand
    Rank: Participant

    Please use the pre-tags when you post code so it's easier to read.

    Whenever troubleshooting SQL stuff, start with checking that it actually works in e.g. SQL Management Studio or whatever SQL tool you use.
    If that doesn't work then it won't work using powershell.

    Also the first query looks abit weird with (SELECT _ID From where Name = '$item')
    You don't state the table name there.

    So start checking if the query works in the first place.

  • #124608

    Participant
    Points: 43
    Rank: Member

    You can also use .NET object SqlConnection for all SQL Server operations. Like this:

    $Sql = "SELECT * FROM [YourTableName] WHERE YouRecordID LIKE 'M100-%'"
    
    $SqlServer = 'SqlServerName'
    $SqlDb = 'MyDbName'
    $SqlUser = 'SqlServerUser'
    $SqlPassword = 'SqlServerPassword'
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=$SqlServer;Database=$SqlDb;User ID=$SqlUser;Password='$SqlPassword'"
    $SqlConnection.Open()
    
    $SqlCommand = New-Object System.Data.SqlClient.SqlCommand($Sql, $SqlConnection)
    
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCommand) 
    $SqlAdapter.Fill($DataSet) > $null
    
    
    foreach($table in $DataSet.Tables.GetEnumerator())
    {
    $table.DefaultView | fl 
    }

    See details here: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=netframework-4.7.2

    Also you can take a look to my test PowerShell script for execution of SQL Server commands: https://1drv.ms/u/s!AmLrzNwOB1OBiZxj2n8AiLUY2gkpCw

    Feel free for ask any questions about this script

  • #124700

    Participant
    Points: 36
    Rank: Member

    Thanks Fredrick and Andy for the suggestions!

    I should have added more details in my initial email.

    I did verify the query in SQL and it works fine and I did use the required connectors as other SQL queries in PS script work fine. Its just the insert statement (below) is not inserting records into DB.

     

    The insert query completes but they are no records inserted and no records in variable $findingsinv. But indeed data exists in DB.

    $items = @("M100","M200","M300","M400","M500","M600")
    
    foreach($item in $items)
    
    {
    
    Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Delete from table1 where MetricID = (Select _ID From table2 where Name = '$item')" -username $dbUsername -password $dbPassword  #delcared server instance earlier and this query works fine
    
    $findingsinv=Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Insert table1(MetricID,Resource,Type,Details,LastModified)
    
     Select (Select _ID From table2 where Name = '$item') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d 
    
    Join table2 c on c._id = d.metricid
    
     where Name like '$item-%'" -username $dbUsername -password $dbPassword 
    • #124772

      Participant
      Points: 43
      Rank: Member

      For inserting you can use the next code:

      $sql = @"
      INSERT INTO table_name (MetricID,Resource,Type,Details,LastModified)
      VALUES values_for_whatever_data_types_you_use
      "@
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = "Server='SqlServer';Database='SqlDb';User ID='SqlUser';Password='MyP@SSword'"
      $SqlConnection.Open()
      
      $SqlCommand = New-Object System.Data.SqlClient.SqlCommand($Sql, $SqlConnection)
      $SQLCommand.CommandText = $Sql
      $SqlCommand.ExecuteNonQuery()
  • #124745

    Participant
    Points: 814
    Helping Hand
    Rank: Major Contributor

    Not a DB guy, an update on DBATools.

  • #124760

    Participant
    Points: 190
    Helping Hand
    Rank: Participant

    Maybe if you try some simple INSERT query as a test first just to see that it actually work?

    E.g. as far as I know you state INSERT INTO

    (you're missing INTO).
    It also might be a good idea to create query as a here-string e.g.

    # NOTE: the last "@ need to be on a new line and at the beginning of that line, so no indentation.
    
    $query = @"
      INSERT INTO table_name (MetricID,Resource,Type,Details,LastModified)
      VALUES values_for_whatever_data_types_you_use
    "@
    

    Except for any parameters that you want to insert in the query using the powershell version of query.
    You can pretty much copy/paste from the standard SQL-statement.

    Meaning if your statement works in e.g. SQL Management Studio.
    Copy that query into a here-string as per example above.
    Does it work?

    If it does then it's not an issue with powershell or the cmdlet.
    It's how the query is constructed.
    Benefit with the here string is that you write that out to console to check that the query actually looks correct when you run it.

  • #125279

    Participant
    Points: 36
    Rank: Member

    Thanks Fredrick, Prasoon and Andy for the suggestions!

    What I noticed was when I give the query with value directly instead of variable like $item, it worked.

    This worked: ( but I had to give this statement 6 times, as we have M100-M600)

    $query6= @" 
    Insert into tabe1 (MetricID,Resource,Type,Details,LastModified)
       Select (Select _ID From table2 where Name = 'M600') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d 
       Join table2 c on c._id = d.metricid where Name like 'M600%'
    "@
    
    $inv =Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query $query -username $dbUsername -password $dbPassword 

    This still does not work when I give as variable $item: No error but values are not inserted into table

     

    items = @("M100","M200","M300","M400","M500","M600")
    
    
    foreach($item in $items)
    
    
    {
    
    
    Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query "Delete from table1 where MetricID = (Select _ID From table2 where Name = '$item')" -username $dbUsername -password $dbPassword 
    $query= @" 
    Insert into tabe1 (MetricID,Resource,Type,Details,LastModified)
       Select (Select _ID From table2 where Name = '$item') As 'MetricID',d.Resource,d.Type,d.Details,d.LastModified from table1 d 
       Join table2 c on c._id = d.metricid where Name like '$item%'
    "@
    
    $inv =Invoke-Sqlcmd2 -serverInstance $dbInstance -Database $dbName -Query $query -username $dbUsername -password $dbPassword

     

     

     

     

  • #125282

    Participant
    Points: 190
    Helping Hand
    Rank: Participant

    Instead of running the query e.g. the $inv line (just comment it out).
    Then do a Write-Output $query.

    Then you can see the whole query without running it, and you can see what the query looks like after the variable have been expanded into it.

You must be logged in to reply to this topic.