locked
How Do You Connect to a Central Management Server If Your Server is a Named Instance RRS feed

  • Question

  • I hope I am in the correct forum. The following code works on a default instance but I am having trouble getting it to work on a named instance.  I have tried replacing the NASY00SQL011DQ in the two spots with CBRTPWTDB204\TI161.

    I get the following error:
    dir : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\CBRTPWTDB204\TI161'.
    At line:50 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

    I have tried replacing the NASY00SQL011DQ in the two spots with CBRTPWTDB204%5CTI161.

    I get the following error:
    dir : SQL Server PowerShell provider error: Invalid Path: 'SQLSERVER:\SQLRegistration\Central Management Server Group\CBRTPWTDB204%5CTI161'.
    At line:50 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

    I cannot figure out what to do next.  Can anyone provide me with a hint where to look next?

     


    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'\NASY00SQL011DQ\ | 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("Server=NASY00SQL011DQ;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
    }

     


    lcerni

    Friday, June 8, 2018 5:29 PM

All replies

  • You have to use both the server and the instance name

     Invoke-sqlcmd -ServerInstance  'servername\instancename'


    \_(ツ)_/

    Friday, June 8, 2018 6:01 PM