August 1, 2023 at 5:07 am
Hi,
The following PowerShell script working and reading records from table, total 600 records of servername list so, I need checking all remote server SQL Server version against 600 servername and results insert into one of SQL Table. could you please guide me how get it results for all remote server SQL version.
$SQLServer = "servername" #use Server\Instance for named SQL instances!
$SQLDBName = "DBName"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;
User ID= Powershell; Password= ****"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = 'select * from Servers_List'
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
August 1, 2023 at 6:14 am
just put it in a "temp" csv file and upload that to sqlserver.
This is how we create such an inventory:
Trap {
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('Trapped error at line [{0}] : [{1}]' -f $ErrorLineNumber, $err.Message );
write-Error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-Error $err.Message
};
write-host $('{0} Script failed - check !!' -f (get-date -format 'yyyy-MM-dd HH:mm:ss') ) -BackgroundColor Red -ForegroundColor Black;
# End the script.
break
}
Clear-Host
# om geen last te hebben van de plaats die wordt ingenomen door Write-Progress
1..10 | % { ('*' * $_ ) }
$TargetDb = 'tempdb'
$Query = @'
SELECT SERVERPROPERTY('ServerName') AS ServerName
, SERVERPROPERTY('InstanceName') AS InstanceName
, SERVERPROPERTY('IsClustered') AS IsClustered
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') AS ProductLevel
, SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel -- SQL 2014 SP1 CU5
, SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion
, SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion
, SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference
, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS
, SERVERPROPERTY('Edition') AS Edition
, @@version as Version
'@;
$ExcludedInstances = @()
$ExcludedInstances += 'not-this-instance'
$TsStart = Get-Date
Write-Host $('{0} Script Start' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss') ) -BackgroundColor Yellow -ForegroundColor Black ;
$ColumnNamesStr = ''
$AllResults = @()
try {
$ActiveSQLInstances = @() ;
$ActiveSQLInstances += get-ActiveSQLInstances -MinSQLversion 2005 -MaxSQLversion 9999 | where Servernaam -NotIn $ExcludedInstances | sort Servernaam ;
$xCtr = 0 ;
$Setcount = $ActiveSQLInstances.count;
foreach ( $inst in $ActiveSQLInstances ){
$xCtr ++ ;
$pct = $xCtr * 100 / $Setcount ;
Write-Progress -Activity $( 'Progressing [{0}] - {1} (ProductVersion {2})' -f $inst.FQDNSServernaam, $inst.ServerDescriptionDb, $inst.ProductVersion ) -Status $('{0} / {1}' -f $xCtr, $Setcount ) -PercentComplete $pct -SourceId 1 ;
$TsStartSrv = Get-Date
try {
$db = Get-SqlDatabase -ServerInstance $inst.FQServernaam -Name $TargetDb -ErrorAction Stop | where Status -eq ([Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal) | where IsUpdateable -eq $true ;
#$dbs = Get-SqlDatabase -ServerInstance $inst.FQServernaam -ErrorAction Stop | where Status -eq ([Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal) | where IsUpdateable -eq $true ;
#foreach ( $db in $dbs ) {
$Results = $db.ExecuteWithResults( $Query ) ;
# $Results = $db.ExecuteNonQuery( $Query ) ;
$AllResults += $Results.Tables[0].rows | Select @{n='FQServernaam';e={$inst.FQServernaam}} , @{n='ServerDescriptionDb';e={$inst.ServerDescriptionDb}}, *
if ( $ColumnNamesStr -eq '' ) {
$ColumnNamesStr = $('FQServernaam,ServerDescriptionDb,{0}' -f $(($Results.Tables[0].Columns | sort ordinal | select -ExpandProperty ColumnName ) -join ',' )) -split ','
#$AllResults += $Results.Tables[0].rows | Select @{n='FQServernaam';e={$inst.FQServernaam}} , @{n='ServerDescriptionDb';e={$inst.ServerDescriptionDb}}, * | Select -Property $ColumnNamesStr
}
#}
$db.Parent.ConnectionContext.Disconnect();
}
catch{
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('{0} Trapped error at line [{1}] ( {2} / {3} ) : [{4}]' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss'), $ErrorLineNumber, $inst.Servernaam, $inst.ServerDescriptionDb, $err.Message );
}
$TsEndSrv = Get-Date
$nDays = [int](New-TimeSpan -Start $TsStartSrv -End $TsEndSrv).TotalDays ;
$nSec = (New-TimeSpan -Start $TsStartSrv -End $TsEndSrv).TotalSeconds ;
Write-Host $('{0} Time needed for {1}: {2} - {3} [{4}]' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss'), $inst.FQServernaam, $nDays, (get-date (( Get-Date -day 1 –month 1 -year 1900 -Hour 0 -Minute 0 -Second 0 ).addseconds( $nSec )) -format 'HH:mm:ss'), $inst.ServerDescriptionDb ) -BackgroundColor Yellow -ForegroundColor Black ;
}
}
catch {
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('{0} Trapped error at line [{1}] : [{2}]' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss') ,$ErrorLineNumber, $err.Message );
write-Error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-error $err.Message
};
}
$TsEnd = Get-Date
$nDays = [int](New-TimeSpan -Start $TsStart -End $TsEnd).TotalDays ;
$nSec = (New-TimeSpan -Start $TsStart -End $TsEnd).TotalSeconds ;
Write-Host $('{0} Script Time needed: {1} - {2}' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss') , $nDays, (get-date (( Get-Date -day 1 –month 1 -year 1900 -Hour 0 -Minute 0 -Second 0 ).addseconds( $nSec )) -format 'HH:mm:ss') ) -BackgroundColor Yellow -ForegroundColor Black ;
$TargetFile = $('{0}\SQLServer_VersionInfo__{1}.csv' -f $env:TEMP, ( get-date -Format 'yyyyMMdd_HHmmss'))
if ( $AllResults.Count -gt 0 ) {
#$AllResults | out-gridview;
if ( Test-Path $TargetFile -PathType Leaf ) {
remove-item $TargetFile -Force
}
$AllResults | Select -Property $ColumnNamesStr | sort ProductVersion, ServerName | export-csv -Path $TargetFile -NoClobber -NoTypeInformation -Delimiter ';' ;
write-host"$targetFile"
#dbatools
Import-DbaCsv -path $targetFile -SqlInstance "myDBAInstance" -Database Inventory -Table InventorySQLVersion -AutoCreateTable
}
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 1, 2023 at 6:30 am
Hi Johan,
Thank you for sharing
I am new to PowerShell scripting , just copied executed in PowerShell ISE but nothing display the results. Please suggest me if something mention (input parameter values) inside script block.
Error Messages.
2023-08-01 11:49:26 Script Start
WARNING: 2023-08-01 11:49:26 Trapped error at line [53] : [The term 'get-ActiveSQLInstances' is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.]
rt-DbaCsv -path $targetFile -SqlInstance "myDBAInstance" -Database Inventory -Table InventorySQLVersion -AutoCreateTable
} : The term 'get-ActiveSQLInstances' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a
path was included, verify that the path is correct and try again.
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException
2023-08-01 11:49:26 Script Time needed: 0 - 00:00:00
August 1, 2023 at 7:17 am
Modified and mentioned my SQL Instance name where target database get inserted from values as below line,, but
Import-DbaCsv -path $targetFile -SqlInstance "DB_servername" -Database DB_Name -Table InventorySQLVersion -AutoCreateTable
below line not working.
$ActiveSQLInstances += get-ActiveSQLInstances -MinSQLversion 2005 -MaxSQLversion 9999 | where Servernaam -NotIn $ExcludedInstances | sort Servernaam ;
2023-08-01 12:39:40 Script Start
WARNING: 2023-08-01 12:39:40 Trapped error at line [53] : [The term 'get-ActiveSQLInstances' is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.]
Thanks
August 1, 2023 at 7:40 am
We keep a DBA inventory database. function get-ActiveSQLInstances reads the active instances information.
This is where you read your server list.
The sets attributs are:
ServerNaam : servername\inctance
Instance : instance
FQServernaam : servername.mydomain.xyz\instance
FQDNSServernaam : myalias\instance
ProductVersion : 2014
SQLServerVersieInfo : Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)
Jul 20 2019 21:42:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
ServerDescriptionDb : _DEV_dba_sql2014
LastTsWijzig : 2023-07-29 22:00:04 ( = datetime of last update of instance information )
You may want to modify those attributes so it makes sense to you.
You'll also have to modify the attributes you use further down in the script.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 3, 2023 at 12:06 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply