• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Powershell comma string to SQL table

The code below returns a comma delimited list, I now need to push this list into a Table in SQL what syntax do I need to Add.



Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=su,DC=sas" | ForEach-Object{

    $dn = $_.DistinguishedName.Split(',')

    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        ParentContainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }

}

Open in new window

0
Leo Torres
Asked:
Leo Torres
  • 17
  • 7
1 Solution
 
Leo TorresSQL DeveloperAuthor Commented:
MS SQL 2008.

As far as the commas.. You are probably right I am seeing it with commas when I display it on to the screen.

If that is an issue I need to address then let me know. My goal is to put this into a table.

Thank you
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No issue, just a formal notice.

The following code looks complex, but it reveals the full power of the .NET Data Adapter, and hence needs a lot of definitions.
I suppose most of it is obvious, but if not, don't hesitate to ask.
# Preparation for MSSQL inserts:
$constr = 'Server=mssqlserve01r\instance02; Database=AD; Integrated Security=true'
$select = 'select * from ADComputers'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'  , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'ParentContainer' ) | out-null
$da.InsertCommand = $cmd
$dt  = New-Object Data.DataTable

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=su,DC=sas" | ForEach-Object{
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        ParentContainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'  ] = $_.Name
  $row['Parent'] = $_.ParentContainer
  $dt.Rows.Add($row)
}

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Something is not right

Error
Column 'Name' does not belong to table .
At line:27 char:3
+   $row['Name'] = $_.Name
+   ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException
 
Column 'Parent' does not belong to table .
At line:28 char:3
+   $row['Parent'] = $_.Parentcontainer
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException

Open in new window


Your Code Line 28 has Parent not Parentcontainer coukd this be an issue?
cls
# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB0X; Database=ReportServerTempDB; Integrated Security=true'
$select = 'select * from ADComputers'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'  , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $cmd
$dt  = New-Object Data.DataTable

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['Parent'] = $_.Parentcontainer
  $dt.Rows.Add($row)
}
                                            

Open in new window


Table Creation
Use [ReportServerTempDB]

Create Table ADComputers(
EntryID Int,
CreationTime DateTime default current_Timestamp,
Parentcontainer varchar(2000),
Name varchar(2000)
)

Open in new window

0
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
Leo TorresSQL DeveloperAuthor Commented:
I did change it to Parentcontainer but it still failed by the way.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
There were two bugs in that script. This one should work now:
cls
# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB0X; Database=ReportServerTempDB; Integrated Security=true'
$select = 'select * from ADComputers where 1=0'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $cmd
$da.Fill($dt)

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
}

Open in new window

Note that we are initialising the DataTable with a SELECT not returning any rows - it is just for getting the necessary table definition for inserting rows.
0
 
Leo TorresSQL DeveloperAuthor Commented:
Ok still not sure I understand the select. There are no error the query runs and returns 0 but no rows were inserted into table

Why you need to know table schema insert specifies the to columns?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
We are working with DataTables here, and they need to have a structure (column definition).

I've missed to perform a commit of the changes (inserts). Add the following line to the end of the script to perform a single commit, or after line 29 to commit each row:
$dt.AcceptChanges()

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Still no insert, no error. Tried with line inside and out side loop


cls
# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB0X; Database=ReportServerTempDB; Integrated Security=true'
$select = 'select * from ADComputers where 1=0'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $cmd
$da.Fill($dt)

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
 # $dt.AcceptChanges()

}

$dt.AcceptChanges()   

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Ran this statement from server to verify my rights

Insert into ADComputers (Name, Parentcontainer)
Select 'Lee', 'Torres'

statement inserted sucessfully
0
 
Leo TorresSQL DeveloperAuthor Commented:
I know for sure the original select is collecting data as I ran the core select and data was return

sample
0
 
Leo TorresSQL DeveloperAuthor Commented:
I added some print screens

cls
# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB03; Database=ReportServerTempDB; Integrated Security=true'
$select = 'Select * from dbo.ADComputers where 1=0'


Write-Host '1'

$con = New-Object Data.SqlClient.SqlConnection ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ($select, $constr)
$dt  = New-Object Data.DataTable

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into dbo.ADComputers( Name, Parentcontainer)
                values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $cmd
$da.Fill($dt)

 Write-Host '2'


Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
   Write-Host '3'
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
 # $dt.AcceptChanges()
}

$dt.AcceptChanges()   

Open in new window


Output
1
0
2
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3

PS AD:\OU=Enterprise Servers,DC=us,DC=saas> 

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Its going thru the loop but no insert.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Hmm. Shouldn't use ADO.NET if I've got no clue how to do it ;-/
cls
Set-StrictMode -Version Latest

# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB03; Database=ReportServerTempDB; Integrated Security=true'

$con = New-Object Data.SqlClient.SqlConnection  ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ('select * from ADComputers where 1=0', $constr)
$dt  = New-Object Data.DataTable('ADComputers')

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into dbo.ADComputers( Name,  Parentcontainer)
                    values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $insert
$da.Fill($dt)

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | 
ForEach-Object {
    $dn = $_.DistinguishedName.Split(',')
    New-Object PSObject -Property @{
        Name = $dn[0] -replace 'cn='
        Parentcontainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU=' 
    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
}
$da.Update($dt)
$con.Close()

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Something wring with array

Index was outside the bounds of the array.
At line:23 char:5
+     New-Object PSObject -Property @{
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], IndexOutOfRangeException
    + FullyQualifiedErrorId : System.IndexOutOfRangeException
 
Index was outside the bounds of the array.

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That's strange, and something outside of the code change I provided. Looks like the LDAP result isn't as expected.
0
 
Leo TorresSQL DeveloperAuthor Commented:
Any new ideas
0
 
Leo TorresSQL DeveloperAuthor Commented:
Ran this code

Get-ADComputer -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
     $_.DistinguishedName.Split(',')
     $dn = $_.DistinguishedName.Split(',')

} |Export-Csv C:\File.csv



Got this (this is only a small Portion)
#TYPE System.String
"Length"
"14"
"7"
"6"
"10"
"5"
"10"
"21"
"5"
"7"
"14"
"7"
"6"
"10"
"5"
"10"
"21"
"5"
"7"
"14"
"7"
"6"
"10"
"5"
"10"
"21"
"5"
"7"
"14"
"7"
"6"
"10"
"5"

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That tells nothing but that there are strings returned.

The error message of http:#a39984824 only can result from a row not having a distinguished name, or only one component in it. Again, the issue is in your original code, so that error should also occur with your code as in the question. $dn[0] is the "culprit" throwing that error.
What we can do is modifying line 22 of http:#a39983842 to make sure we always have an array:
    $dn = @($_.DistinguishedName.Split(','))

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
The issue is with this Line

 ParentContainer = $dn[$dn.length..1] -notlike 'dc=*' -join ',' -replace 'OU='

I was not getting this error before

Index was outside the bounds of the array.
At line:34 char:4
+    New-Object PSObject -Property @{
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], IndexOutOfRangeException
    + FullyQualifiedErrorId : System.IndexOutOfRangeException
 
Index was outside the bounds of the array.

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
If I comment out the Line above I get all the values in [0] but I don't get parent tree results which its what I need.


FYI i did  make that change you posted above and it still errored

Code
0
 
Leo TorresSQL DeveloperAuthor Commented:
In my opinon I belive it may have something to do with the variables lengths of each Server.

you may have server 3 folders deep or a server 8 Folders deep.


I ran this and it returned values but it errored out when more than 9  
$dn[1] + 
                             ','+ $dn[2] +
                             ','+ $dn[3] +
                             ','+ $dn[4] +
                             ','+ $dn[5] +
                             ','+ $dn[6] +
                             ','+ $dn[7] +
                             ','+ $dn[8] 

Open in new window




code2
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I insist on that being an error from the very start (i.e the code you provided). The index of this expression is too big (by one). $dn.Length is e.g. 3 if three elements are in that array, but the index starts with 0, so allowable indexes are 0..2 and not 0..3.

It "worked" because PS ignored that bug. My Set-StrictMode statements involves index boundary checks, and hence you now get an error.

You can remove the Set-StrictMode line, or correct the index usage:
ParentContainer = $dn[($dn.length-1)..1] -notlike 'dc=*' -join ',' -replace 'OU='

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Working code thanks
cls
Set-StrictMode -Version Latest

# Preparation for MSSQL inserts:
$constr = 'Server=EZ1SUP1DB03; Database=ReportServerTempDB; Integrated Security=true'

$con = New-Object Data.SqlClient.SqlConnection  ($constr)
$da  = New-Object Data.SqlClient.SqlDataAdapter ('select * from ADComputers where 1=0', $constr)
$dt  = New-Object Data.DataTable('ADComputers')

$insert = New-Object Data.SqlClient.SqlCommand (@'
insert into dbo.ADComputers( Name,  Parentcontainer)
                    values (@Name, @Parent)
'@, $con)
$insert.Parameters.Add('@Name'   , 'varchar',  30, 'Name'            ) | out-null
$insert.Parameters.Add('@Parent' , 'varchar', 200, 'Parentcontainer' ) | out-null
$da.InsertCommand = $insert
$da.Fill($dt)

Get-ADComputer -LDAPFilter "(DistinguishedName=*)" -SearchScope Subtree -SearchBase "OU=Enterprise Servers,DC=us,DC=saas" | ForEach-Object{
     
      $dn = @($_.DistinguishedName.Split(','))
    
   New-Object PSObject -Property @{
        Name = ',' + $dn[0] -replace 'cn='
        ParentContainer = $dn[($dn.length-1)..1] -notlike 'dc=*' -join ',' -replace 'OU='

    }
} | % {
  $row = $dt.NewRow()
  $row['Name'] = $_.Name
  $row['ParentContainer'] = $_.Parentcontainer
  $dt.Rows.Add($row)
}
$da.Update($dt)
$con.Close()

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Thank you for your fast and accurate help..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 17
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now