Welcome › Forums › General PowerShell Q&A › Need powershell script to update the SQL database
- This topic has 4 replies, 3 voices, and was last updated 1 month, 3 weeks ago by
Participant.
-
AuthorPosts
-
-
October 16, 2019 at 6:12 pm #183303
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")
-
October 17, 2019 at 6:54 am #183447
Hi Sekhar,
The script looks good to me. What is the issue here?
Thank you.
-
October 17, 2019 at 11:28 am #183483
When I tried to run this script on the SQL server. it's throwing an error as function ExecuteSqlQuery is not recognized .
-
-
October 17, 2019 at 12:18 pm #183513
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")
-
October 17, 2019 at 7:37 pm #183654
Possible simplification – the SqlServer module has Invoke-Sqlcmd.
-
-
AuthorPosts
- You must be logged in to reply to this topic.