Need powershell script to update the SQL database

Welcome Forums General PowerShell Q&A Need powershell script to update the SQL database

Viewing 3 reply threads
  • Author
    Posts
    • #183303
      Participant
      Topics: 2
      Replies: 2
      Points: 21
      Rank: Member

        SQL command:

        update [Ascertain_PROD].dbo.HostStatus

        set Comments = 'Decommissioned' where HostID in (SELECT  H.[HostID]

        FROM [Ascertain_PROD].[dbo].[Host] H left join [Ascertain_PROD].dbo.HostStatus HS on H.HostID = HS.HostID

        where h.HostName = 'XXXXXServerNameXXXXX')

      We need to create a powershell script using above update sql command to run remotely to update the database . Once run the script, server will be updated as decommissioned in our inventory and it should run using credentails. please help me to get the script to update.

      Example:

      [string]$sqlserver = "winsqla13.contonso.com"
      [string]$sqllogin = "username"
      [string]$sqlpwd = "password"
      [string] $Database = "Ascertain"
      [string] $UserSqlQuery= $("update [Ascertain_PROD].dbo.HostStatus
      set Comments = 'Decommissioned' where HostID in (SELECT H.[HostID]
      FROM [Ascertain_PROD].[dbo].[Host] H left join [Ascertain_PROD].dbo.HostStatus HS on H.HostID = HS.HostID
      where h.HostName = 'XXXXXServerNameXXXXX')
      ")
      
      # declaration not necessary, but good practice
      $resultsDataTable = New-Object System.Data.DataTable
      $resultsDataTable = ExecuteSqlQuery $sqlserver $Database $UserSqlQuery
      
      # executes a query and populates the $datatable with the data
      function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
      $Datatable = New-Object System.Data.DataTable
      
      $Connection = New-Object System.Data.SQLClient.SQLConnection
      $Connection.ConnectionString = "server='$sqlserver';database='$Database';trusted_connection=true;"
      $Connection.Open()
      $Command = New-Object System.Data.SQLClient.SQLCommand
      $Command.Connection = $Connection
      $Command.CommandText = $SQLQuery
      $Reader = $Command.ExecuteReader()
      $Datatable.Load($Reader)
      $Connection.Close()
      
      return $Datatable
      }
      
      #validate we got data
      Write-Host ("The table contains: " + $resultsDataTable.Rows.Count + " rows")
      
    • #183447
      Participant
      Topics: 2
      Replies: 135
      Points: 708
      Helping Hand
      Rank: Major Contributor

      Hi Sekhar,

      The script looks good to me. What is the issue here?

      Thank you.

      • #183483
        Participant
        Topics: 2
        Replies: 2
        Points: 21
        Rank: Member

        When I tried to run this script on the SQL server. it's throwing an error as function ExecuteSqlQuery is not recognized .

    • #183513
      Participant
      Topics: 2
      Replies: 135
      Points: 708
      Helping Hand
      Rank: Major Contributor

      Yes, call the function after the function is created.

      
      [string]$sqlserver = "winsqla13.contonso.com"
      [string]$sqllogin = "username"
      [string]$sqlpwd = "password"
      [string] $Database = "Ascertain"
      [string] $UserSqlQuery= $("update [Ascertain_PROD].dbo.HostStatus
      set Comments = 'Decommissioned' where HostID in (SELECT H.[HostID]
      FROM [Ascertain_PROD].[dbo].[Host] H left join [Ascertain_PROD].dbo.HostStatus HS on H.HostID = HS.HostID
      where h.HostName = 'XXXXXServerNameXXXXX')
      ")
      
      # executes a query and populates the $datatable with the data
      function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
      $Datatable = New-Object System.Data.DataTable
      
      $Connection = New-Object System.Data.SQLClient.SQLConnection
      $Connection.ConnectionString = "server='$sqlserver';database='$Database';trusted_connection=true;"
      $Connection.Open()
      $Command = New-Object System.Data.SQLClient.SQLCommand
      $Command.Connection = $Connection
      $Command.CommandText = $SQLQuery
      $Reader = $Command.ExecuteReader()
      $Datatable.Load($Reader)
      $Connection.Close()
      
      return $Datatable
      }
      
      # declaration not necessary, but good practice
      $resultsDataTable = New-Object System.Data.DataTable
      $resultsDataTable = ExecuteSqlQuery $sqlserver $Database $UserSqlQuery
      
      #validate we got data
      Write-Host ("The table contains: " + $resultsDataTable.Rows.Count + " rows")
      
      

       

    • #183654
      Participant
      Topics: 9
      Replies: 36
      Points: 156
      Helping Hand
      Rank: Participant

      Possible simplification – the SqlServer module has Invoke-Sqlcmd.

Viewing 3 reply threads
  • You must be logged in to reply to this topic.