Need powershell script to update the SQL database

PowerShell Help
1

<p style=“margin: 0in; margin-bottom: .0001pt;”> SQL command:</p>
<p style=“margin: 0in; margin-bottom: .0001pt;”> update [Ascertain_PROD].dbo.HostStatus</p>
<p style=“margin: 0in; margin-bottom: .0001pt;”> set Comments = ‘Decommissioned’ where HostID in (SELECT H.[HostID]</p>
<p style=“margin: 0in; margin-bottom: .0001pt;”> FROM [Ascertain_PROD].[dbo].[Host] H left join [Ascertain_PROD].dbo.HostStatus HS on H.HostID = HS.HostID</p>
<p style=“margin: 0in; margin-bottom: .0001pt;”> where h.HostName = ‘XXXXXServerNameXXXXX’)</p>
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")
2

Hi Sekhar,

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

Thank you.

3

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

4

Yes, call the function after the function is created.

[pre]

[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”)

[/pre]

 

5

Possible simplification - the SqlServer module has Invoke-Sqlcmd.