June 5, 2018 at 9:24 am
I have the following code that worked on a default instance. Now I am trying to get it to work on a named instance. I have tried replacing the \ with %5C. That didn't work. Any help would be greatly appreciated.
#######################
<#
.SYNOPSIS
Creates a DataTable for an object
.DESCRIPTION
Creates a DataTable based on an objects properties.
.INPUTS
Object
Any object can be piped to Out-DataTable
.OUTPUTS
System.Data.DataTable
.EXAMPLE
$dt = Get-Alias | Out-DataTable
This example creates a DataTable from the properties of Get-Alias and assigns output to $dt variable
.NOTES
Adapted from script by Marc van Orsouw see link
Version History
v1.0 - Chad Miller - Initial Release
v1.1 - Chad Miller - Fixed Issue with Properties
.LINK
http://thepowershellguy.com/blogs/posh/archive/2007/01/21/powershell-gui-scripblock-monitor-script.aspx
#>
function Out-DataTable
{
[CmdletBinding()]
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)
Begin
{
$dt = new-object Data.datatable
$First = $true
}
Process
{
foreach ($object in $InputObject)
{
$DR = $DT.NewRow()
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
$Col = new-object Data.DataColumn
$Col.ColumnName = $property.Name.ToString()
$DT.Columns.Add($Col)
}
if ($property.IsArray)
{ $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 }
else { $DR.Item($property.Name) = $property.value }
}
$DT.Rows.Add($DR)
$First = $false
}
}
End
{
Write-Output @(,($dt))
}
} #Out-DataTable
Import-Module “sqlps” -DisableNameChecking
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Management Server Group'\"CBRTPWTDB204\TI161"\ | where {$_.Mode -ne "d"} )
{
$dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "tempdb" -InputFile "D:\CMS\Scripts\T-SQL\Databases.sql" | out-DataTable
$dt
# Write data table to database using TVP
$conn = new-Object System.Data.SqlClient.SqlConnection("data source=CBRTPWTDB204\TI161;DataBase=CMSRepository;Integrated Security=SSPI")
$conn.Open() | out-null
"Connected"
$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.usp_InsertDatabases", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
#SQLParameter
$spParam = new-Object System.Data.SqlClient.SqlParameter
$spParam.ParameterName = "@TVP"
$spParam.Value = $dt
$spParam.SqlDbType = "Structured" #SqlDbType.Structured
$spParam.TypeName = "Databases"
$cmd.Parameters.Add($spParam) | out-Null
$cmd.ExecuteNonQuery() | out-Null
$conn.Close() | out-Null
}
This is the message that I am getting when I run the above code:
PS SQLSERVER:\> D:\CMS\Scripts\PowerShell\Databases.ps1
dir : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\CBRTPWTDB204\TI161'.
At D:\CMS\Scripts\PowerShell\Databases.ps1:74 char:29
+ foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Man ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-ChildItem], GenericProviderException
+ FullyQualifiedErrorId : Microsoft.SqlServer.Management.PowerShell.GenericProviderException,Microsoft.PowerShell.Commands.GetChildItemCommand
June 5, 2018 at 10:07 am
L Cerniglia - Tuesday, June 5, 2018 9:24 AMI have the following code that worked on a default instance. Now I am trying to get it to work on a named instance. I have tried replacing the \ with %5C. That didn't work. Any help would be greatly appreciated.
#######################
<#
.SYNOPSIS
Creates a DataTable for an object
.DESCRIPTION
Creates a DataTable based on an objects properties.
.INPUTS
Object
Any object can be piped to Out-DataTable
.OUTPUTS
System.Data.DataTable
.EXAMPLE
$dt = Get-Alias | Out-DataTable
This example creates a DataTable from the properties of Get-Alias and assigns output to $dt variable
.NOTES
Adapted from script by Marc van Orsouw see link
Version History
v1.0 - Chad Miller - Initial Release
v1.1 - Chad Miller - Fixed Issue with Properties
.LINK
http://thepowershellguy.com/blogs/posh/archive/2007/01/21/powershell-gui-scripblock-monitor-script.aspx
#>
function Out-DataTable
{
[CmdletBinding()]
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)Begin
{
$dt = new-object Data.datatable
$First = $true
}
Process
{
foreach ($object in $InputObject)
{
$DR = $DT.NewRow()
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
$Col = new-object Data.DataColumn
$Col.ColumnName = $property.Name.ToString()
$DT.Columns.Add($Col)
}
if ($property.IsArray)
{ $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 }
else { $DR.Item($property.Name) = $property.value }
}
$DT.Rows.Add($DR)
$First = $false
}
}
End
{
Write-Output @(,($dt))
}} #Out-DataTable
Import-Module “sqlps†-DisableNameChecking
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Management Server Group'\"CBRTPWTDB204\TI161"\ | where {$_.Mode -ne "d"} )
{
$dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "tempdb" -InputFile "D:\CMS\Scripts\T-SQL\Databases.sql" | out-DataTable
$dt
# Write data table to database using TVP
$conn = new-Object System.Data.SqlClient.SqlConnection("data source=CBRTPWTDB204\TI161;DataBase=CMSRepository;Integrated Security=SSPI")
$conn.Open() | out-null
"Connected"
$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.usp_InsertDatabases", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
#SQLParameter
$spParam = new-Object System.Data.SqlClient.SqlParameter
$spParam.ParameterName = "@TVP"
$spParam.Value = $dt
$spParam.SqlDbType = "Structured" #SqlDbType.Structured
$spParam.TypeName = "Databases"
$cmd.Parameters.Add($spParam) | out-Null
$cmd.ExecuteNonQuery() | out-Null
$conn.Close() | out-Null
}This is the message that I am getting when I run the above code:
PS SQLSERVER:\> D:\CMS\Scripts\PowerShell\Databases.ps1
dir : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\CBRTPWTDB204\TI161'.
At D:\CMS\Scripts\PowerShell\Databases.ps1:74 char:29
+ foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Man ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-ChildItem], GenericProviderException
+ FullyQualifiedErrorId : Microsoft.SqlServer.Management.PowerShell.GenericProviderException,Microsoft.PowerShell.Commands.GetChildItemCommand
It's due to the backslash in the instance name. Replace it with %5C so something like:
'SQLSERVER:\SQLRegistration\Central Management Server Group\CBRTPWTDB204%5CTI161'
Sue
June 5, 2018 at 11:00 am
I have replaced with %5CT
And this is the message that I am getting
PS SQLSERVER:\> D:\CMS\Scripts\PowerShell\Databases.ps1
dir : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\CBRTPWTDB204%5CTI161'.
At D:\CMS\Scripts\PowerShell\Databases.ps1:74 char:29
+ foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Man ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-ChildItem], GenericProviderException
+ FullyQualifiedErrorId : Microsoft.SqlServer.Management.PowerShell.GenericProviderException,Microsoft.PowerShell.Commands.GetChildItemCommand
#######################
<#
.SYNOPSIS
Creates a DataTable for an object
.DESCRIPTION
Creates a DataTable based on an objects properties.
.INPUTS
Object
Any object can be piped to Out-DataTable
.OUTPUTS
System.Data.DataTable
.EXAMPLE
$dt = Get-Alias | Out-DataTable
This example creates a DataTable from the properties of Get-Alias and assigns output to $dt variable
.NOTES
Adapted from script by Marc van Orsouw see link
Version History
v1.0 - Chad Miller - Initial Release
v1.1 - Chad Miller - Fixed Issue with Properties
.LINK
#>
function Out-DataTable
{
[CmdletBinding()]
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)
Begin
{
$dt = new-object Data.datatable
$First = $true
}
Process
{
foreach ($object in $InputObject)
{
$DR = $DT.NewRow()
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
$Col = new-object Data.DataColumn
$Col.ColumnName = $property.Name.ToString()
$DT.Columns.Add($Col)
}
if ($property.IsArray)
{ $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 }
else { $DR.Item($property.Name) = $property.value }
}
$DT.Rows.Add($DR)
$First = $false
}
}
End
{
Write-Output @(,($dt))
}
} #Out-DataTable
Import-Module “sqlps” -DisableNameChecking
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Central Management Server Group'\CBRTPWTDB204%5CTI161\ | where {$_.Mode -ne "d"} )
{
$dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "tempdb" -InputFile "D:\CMS\Scripts\T-SQL\Databases.sql" | out-DataTable
$dt
# Write data table to database using TVP
$conn = new-Object System.Data.SqlClient.SqlConnection("data source=CBRTPWTDB204\TI161;DataBase=CMSRepository;Integrated Security=SSPI")
$conn.Open() | out-null
"Connected"
$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.usp_InsertDatabases", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
#SQLParameter
$spParam = new-Object System.Data.SqlClient.SqlParameter
$spParam.ParameterName = "@TVP"
$spParam.Value = $dt
$spParam.SqlDbType = "Structured" #SqlDbType.Structured
$spParam.TypeName = "Databases"
$cmd.Parameters.Add($spParam) | out-Null
$cmd.ExecuteNonQuery() | out-Null
$conn.Close() | out-Null
}
June 5, 2018 at 2:01 pm
Okay...I see what you are doing. For each instance, try using this instead. If you have a group, add that to the end with \GroupName. And that's it:
Get-Childitem 'SQLSERVER:\SQLRegistration\Central Management Server Group'
If you still get errors, try building it all a bit more slowly.
After importing SQLServer (or SQLPS which is the old module), try executing just that line and see if you get the CMS instances back.
Then build from there.
foreach ($RegisteredSQLs in dir -recurse 'SQLSERVER:\SQLRegistration\Central Management Server Group' | where {$_.Mode -ne "d"} )
{
....
}
Sue
June 5, 2018 at 5:51 pm
When I run
Get-Childitem 'SQLSERVER:\SQLRegistration\Central Management Server Group'
I just get
PS SQLSERVER:\>
Get-Childitem 'SQLSERVER:\SQLRegistration\Central Management Server Group'
PS SQLSERVER:\>
June 5, 2018 at 5:53 pm
When I run the following..
Function Parse-ServerGroup($serverGroup)
{
$results = $serverGroup.RegisteredServers;
foreach($group in $serverGroup.ServerGroups)
{
$results += Parse-ServerGroup -serverGroup $group;
}
return $results;
}
Function Get-ServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse)
{
$connectionString = "data source=$cmsName;initial catalog=master;integrated security=sspi;"
$sqlConnection = New-Object ("System.Data.SqlClient.SqlConnection") $connectionstring
$conn = New-Object ("Microsoft.SQLServer.Management.common.serverconnection") $sqlconnection
$cmsStore = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)
$cmsRootGroup = $cmsStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$serverGroup]
if($recurse)
{
return Parse-ServerGroup -serverGroup $cmsRootGroup | select ServerName
}
else
{
return $cmsRootGroup.RegisteredServers | select ServerName
}
}
Get-ServerList -cmsName "CBRTPWTDB204\TI161" -serverGroup "Production" -recurse | Format-Table
I get
ServerName
----------
ATLHC001
CBBRBWPDB200
CBRTPWPAP44
CBRTPWPDB09
CBRTPWPDB12
CBRTPWPDB20
CBRTPWPDB200
CBRTPWPDB203\VCM
CBRTPWPDB205
CBRTPWPDB21
CBRTPWPDB215\PI141
CBRTPWPDB216\PI121
CBRTPWPDB216\PI141
CBRTPWPDB216\PI161
CBRTPWPDB217\PI121
CBRTPWPDB218
CBRTPWPDB219
CBRTPWPDB222
CBRTPWPDB25
CBRTPWPDB27
CBRTPWPDBU226
CBRTPWPDBU228
CBRTPWPDBU229
CBRTPWPDBU230
CBRTPWPDBU232
CBRTPWPDBU233
CBRTPWPDBU234
CBRTPWPDBU235\PI121
CBRTPWPDBU237
CBRTPWPDBU238
CBRTPWPDBU28
CBRTPWPHCARC01
CBRTPWPVCSDB01
CBRTPWPVCSDB04
CBRTPWPWA67
CBRTPWPWA68
CBRTPWPWA69
CBRTPWTDB03
CBWTOWPDB200
CBWTOWPDB202
CBWTOWPDB205
CBWTOWPDBU203
CHBDA3UCCE01
CHBDA3UCCE02
CHBDA3UCCE03
CHBDA3UCCE07
CHBWARUCCE01
CHBWARUCCE02
CHIHC001
DALHC001
DTOHC001
PS SQLSERVER:\>
June 5, 2018 at 5:54 pm
I am confused. I am not sure how to change the for loop.
June 6, 2018 at 4:50 pm
L Cerniglia - Tuesday, June 5, 2018 5:54 PMI am confused. I am not sure how to change the for loop.
You would do whatever you plan on doing in the brackets. It's just pulling up the server name in this:
$Servers = Get-ServerList -cmsName "CBRTPWTDB204\TI161" -serverGroup "Production" -recurse
ForEach ($Server in $Servers)
{$Server}
June 8, 2018 at 10:17 am
So this is how the code looks now
#######################
<#
.SYNOPSIS
Creates a DataTable for an object
.DESCRIPTION
Creates a DataTable based on an objects properties.
.INPUTS
Object
Any object can be piped to Out-DataTable
.OUTPUTS
System.Data.DataTable
.EXAMPLE
$dt = Get-Alias | Out-DataTable
This example creates a DataTable from the properties of Get-Alias and assigns output to $dt variable
.NOTES
Adapted from script by Marc van Orsouw see link
Version History
v1.0 - Chad Miller - Initial Release
v1.1 - Chad Miller - Fixed Issue with Properties
.LINK
http://thepowershellguy.com/blogs/posh/archive/2007/01/21/powershell-gui-scripblock-monitor-script.aspx
#>
function Out-DataTable
{
[CmdletBinding()]
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)
Begin
{
$dt = new-object Data.datatable
$First = $true
}
Process
{
foreach ($object in $InputObject)
{
$DR = $DT.NewRow()
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
$Col = new-object Data.DataColumn
$Col.ColumnName = $property.Name.ToString()
$DT.Columns.Add($Col)
}
if ($property.IsArray)
{ $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 }
else { $DR.Item($property.Name) = $property.value }
}
$DT.Rows.Add($DR)
$First = $false
}
}
End
{
Write-Output @(,($dt))
}
} #Out-DataTable
Import-Module “sqlps” -DisableNameChecking
$Servers = Get-ServerList -cmsName "CBRTPWTDB204\TI161" -serverGroup "Production" -recurse
ForEach ($Server in $Servers)
{
$dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "tempdb" -InputFile "D:\CMS\Scripts\T-SQL\Databases.sql" | out-DataTable
$dt
# Write data table to database using TVP
$conn = new-Object System.Data.SqlClient.SqlConnection("Server=CBRTPWTDB204\TI161;DataBase=CMSRepository;Integrated Security=SSPI")
$conn.Open() | out-null
"Connected"
$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.usp_InsertDatabases", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
#SQLParameter
$spParam = new-Object System.Data.SqlClient.SqlParameter
$spParam.ParameterName = "@TVP"
$spParam.Value = $dt
$spParam.SqlDbType = "Structured" #SqlDbType.Structured
$spParam.TypeName = "Databases"
$cmd.Parameters.Add($spParam) | out-Null
$cmd.ExecuteNonQuery() | out-Null
$conn.Close() | out-Null
}
This is the error message that I am now getting:
Connected
Invoke-sqlcmd : Value cannot be null.
Parameter name: ServerInstance
At line:74 char:7
+ $dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "t ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Invoke-Sqlcmd], ArgumentNullException
+ FullyQualifiedErrorId : CannotGetServerInstance,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
June 8, 2018 at 10:48 am
L Cerniglia - Friday, June 8, 2018 10:17 AMSo this is how the code looks now
#######################
<#
.SYNOPSIS
Creates a DataTable for an object
.DESCRIPTION
Creates a DataTable based on an objects properties.
.INPUTS
Object
Any object can be piped to Out-DataTable
.OUTPUTS
System.Data.DataTable
.EXAMPLE
$dt = Get-Alias | Out-DataTable
This example creates a DataTable from the properties of Get-Alias and assigns output to $dt variable
.NOTES
Adapted from script by Marc van Orsouw see link
Version History
v1.0 - Chad Miller - Initial Release
v1.1 - Chad Miller - Fixed Issue with Properties
.LINK
http://thepowershellguy.com/blogs/posh/archive/2007/01/21/powershell-gui-scripblock-monitor-script.aspx
#>
function Out-DataTable
{
[CmdletBinding()]
param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)Begin
{
$dt = new-object Data.datatable
$First = $true
}
Process
{
foreach ($object in $InputObject)
{
$DR = $DT.NewRow()
foreach($property in $object.PsObject.get_properties())
{
if ($first)
{
$Col = new-object Data.DataColumn
$Col.ColumnName = $property.Name.ToString()
$DT.Columns.Add($Col)
}
if ($property.IsArray)
{ $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1 }
else { $DR.Item($property.Name) = $property.value }
}
$DT.Rows.Add($DR)
$First = $false
}
}
End
{
Write-Output @(,($dt))
}} #Out-DataTable
Import-Module “sqlps†-DisableNameChecking
$Servers = Get-ServerList -cmsName "CBRTPWTDB204\TI161" -serverGroup "Production" -recurse
ForEach ($Server in $Servers)
{
$dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "tempdb" -InputFile "D:\CMS\Scripts\T-SQL\Databases.sql" | out-DataTable
$dt
# Write data table to database using TVP
$conn = new-Object System.Data.SqlClient.SqlConnection("Server=CBRTPWTDB204\TI161;DataBase=CMSRepository;Integrated Security=SSPI")
$conn.Open() | out-null
"Connected"
$cmd = new-Object System.Data.SqlClient.SqlCommand("dbo.usp_InsertDatabases", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
#SQLParameter
$spParam = new-Object System.Data.SqlClient.SqlParameter
$spParam.ParameterName = "@TVP"
$spParam.Value = $dt
$spParam.SqlDbType = "Structured" #SqlDbType.Structured
$spParam.TypeName = "Databases"
$cmd.Parameters.Add($spParam) | out-Null
$cmd.ExecuteNonQuery() | out-Null
$conn.Close() | out-Null
}This is the error message that I am now getting:
Connected
Invoke-sqlcmd : Value cannot be null.
Parameter name: ServerInstance
At line:74 char:7
+ $dt = Invoke-sqlcmd -ServerInstance "$($RegisteredSQLs.ServerName)" -Database "t ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Invoke-Sqlcmd], ArgumentNullException
+ FullyQualifiedErrorId : CannotGetServerInstance,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
You're passing this for the instance: "$($RegisteredSQLs.ServerName)"
But the instance is $Server
Sue
June 8, 2018 at 12:35 pm
I alias the group names when registering them, especially for servers with named instances.
Server name: ACTUALSERVER\INSTANCE
Registered server name: XYZ servers
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply