Need powershell script to update the SQL database

[sekhar4056 Participant Topics: 2 Replies: 2 Points: 21Rank: Member]

<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:

PowerShell

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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

[Kiran Moderator Topics: 5 Replies: 237 Points: 1,320Rank: Community Hero]

Hi Sekhar,

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

Thank you.

[sekhar4056 Participant Topics: 2 Replies: 2 Points: 21Rank: Member]

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

[Kiran Moderator Topics: 5 Replies: 237 Points: 1,320Rank: Community Hero]

Yes, call the function after the function is created.

PowerShell

[string]$sqlserver = &#8220;winsqla13.contonso.com&#8221; [string]$sqllogin = &#8220;username&#8221; [string]$sqlpwd = &#8220;password&#8221; [string] $Database = &#8220;Ascertain&#8221; [string] $UserSqlQuery= $(&#8220;update [Ascertain_PROD].dbo.HostStatus set Comments = &#8216;Decommissioned&#8217; 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 = &#8216;XXXXXServerNameXXXXX&#8217;) &#8220;) # 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 = &#8220;server=&#8217;$sqlserver&#8217;;database=&#8217;$Database&#8217;;trusted_connection=true;&#8221; $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 (&#8220;The table contains: &#8221; + $resultsDataTable.Rows.Count + &#8221; rows&#8221;)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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

 

[Darwin Reiswig Participant Topics: 10 Replies: 41 Points: 213Rank: Participant]

Possible simplification – the SqlServer module has Invoke-Sqlcmd.

[Author]

Posts