Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
AD Group Members to SQL Table
Here is my powershell statement with associated error. Can't figure out what I am doing wrong.
PS H:\> Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
Select-Object @{Name="Group Name";Expression={$Group}}
} | out-host
$conn = New-Object System.Data.SqlClient.SqlC
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers('$Results')
$cmd = $conn.CreateCommand()
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()
Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near ')'."
At line:11 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Any assistance would be greatly appreciated. Also I am planning on running this script once a day and would probably like to drop and re-add the table with the new data. Insight into this would also be beneficial
PS H:\> Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
Select-Object @{Name="Group Name";Expression={$Group}}
} | out-host
$conn = New-Object System.Data.SqlClient.SqlC
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers('$Results')
$cmd = $conn.CreateCommand()
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()
Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near ')'."
At line:11 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Any assistance would be greatly appreciated. Also I am planning on running this script once a day and would probably like to drop and re-add the table with the new data. Insight into this would also be beneficial
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It may be expecting a VALUES statement:
INSERT INTO ADGroupMembers VALUES ('$Results')
http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx
INSERT INTO ADGroupMembers VALUES ('$Results')
http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx
I am not sure about the SQL part but | out-host after } of each object will only result in the last searched result
you should include | out-host within the } after ,name
you should include | out-host within the } after ,name
Getting closer but now get this (see screenshot).
Also the table names in the table are exactly the same in the output "Group Name" and "Name" set to nvarchar(50)
Also the table names in the table are exactly the same in the output "Group Name" and "Name" set to nvarchar(50)
Okay, so the error is self-explanatory - "the number of supplied values does not match the table definition" so you either need to tell it what column the values go into or you need to supply values for all the columns.
This is what I am trying to figure out. what should be there. I tried this
Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
Select-Object @{Name="GroupName";Express
} | out-host
$conn = New-Object System.Data.SqlClient.SqlC
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers VALUES($Results.GroupName,$Result
$cmd = $conn.CreateCommand()
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()
Exception calling "ExecuteNonQuery" with "0" argument(s): "The name ".GroupName" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:11 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
Select-Object @{Name="GroupName";Express
} | out-host
$conn = New-Object System.Data.SqlClient.SqlC
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers VALUES($Results.GroupName,$Result
$cmd = $conn.CreateCommand()
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()
Exception calling "ExecuteNonQuery" with "0" argument(s): "The name ".GroupName" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:11 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
"This is what I am trying to figure out. what should be there."
What's your table's structure? Generally the command takes the form of:
INSERT INTO table (columnname1, columnname2, columnname3) VALUES (value1, value2, value3)
What's your table's structure? Generally the command takes the form of:
INSERT INTO table (columnname1, columnname2, columnname3) VALUES (value1, value2, value3)
results provides two columns, "GroupName" and "name" as seen in the screenshot above, the table contains the exact two column names. Essentially I need the results from
Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
Select-Object @{Name="GroupName";Express
}
entered into a SQL Table called ADGroupMembers that has two columns as well (GroupName, Name)
Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group |
Select-Object @{Name="GroupName";Express
}
entered into a SQL Table called ADGroupMembers that has two columns as well (GroupName, Name)
maybe my approach is all wrong to start with and there is a better way. I need to take groups from a text file, query AD and find all the users that are in those said groups then take the results and insert the group name and the member into a sql table. Is there another approach I should be taking for this? Thanks again as I am new to powershell (as you can tell) and trying to piece together stuff from google to get what I need.
Try...
$insert_stmt = "INSERT INTO ADGroupMembers (GroupName, Name) VALUES ($Results.GroupName, $Results.Name)"
$insert_stmt = "INSERT INTO ADGroupMembers (GroupName, Name) VALUES ($Results.GroupName, $Results.Name)"
Get this error.
INSERT INTO ADGroupMembers (GroupName, Name) VALUES(.groupname, .name)
-1
Exception calling "ExecuteNonQuery" with "0" argument(s): "The name ".groupname" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:16 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
INSERT INTO ADGroupMembers (GroupName, Name) VALUES(.groupname, .name)
-1
Exception calling "ExecuteNonQuery" with "0" argument(s): "The name ".groupname" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:16 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Still no go...
INSERT INTO ADGroupMembers (GroupName, Name) VALUES(.groupname, .name)
-1
Exception calling "ExecuteScalar" with "0" argument(s): "The name ".groupname" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:16 char:1
+ $cmd.ExecuteScalar()
+ ~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
when I try this I see all the user info under 1 row.
Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group
# Select-Object @{Name="GroupName";Express
}
$conn = New-Object System.Data.SqlClient.SqlC
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers VALUES('"+ $group +"','"+ $Results +"')"
write $insert_stmt
$cmd = $conn.CreateCommand()
#delete existing contents of this table (enter SysTrack DB Name below)
$cmd.CommandText = "truncate table " + $db + ".ADGroupMembers"
$cmd.ExecuteNonQuery()
#import new/current data
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()
INSERT INTO ADGroupMembers VALUES('ACL_Builds','CN=Li
pport Users,OU=Support,OU=HQ,DC=
-1
Exception calling "ExecuteNonQuery" with "0" argument(s): "String or binary data would be truncated.
The statement has been terminated."
At line:16 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
INSERT INTO ADGroupMembers (GroupName, Name) VALUES(.groupname, .name)
-1
Exception calling "ExecuteScalar" with "0" argument(s): "The name ".groupname" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not
permitted."
At line:16 char:1
+ $cmd.ExecuteScalar()
+ ~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
when I try this I see all the user info under 1 row.
Get-Content C:\temp\groups.txt | ForEach-Object {
$Group = $_
$Results = Get-ADGroupMember $Group
# Select-Object @{Name="GroupName";Express
}
$conn = New-Object System.Data.SqlClient.SqlC
$conn.Open()
$insert_stmt = "INSERT INTO ADGroupMembers VALUES('"+ $group +"','"+ $Results +"')"
write $insert_stmt
$cmd = $conn.CreateCommand()
#delete existing contents of this table (enter SysTrack DB Name below)
$cmd.CommandText = "truncate table " + $db + ".ADGroupMembers"
$cmd.ExecuteNonQuery()
#import new/current data
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
$conn.Close()
INSERT INTO ADGroupMembers VALUES('ACL_Builds','CN=Li
pport Users,OU=Support,OU=HQ,DC=
-1
Exception calling "ExecuteNonQuery" with "0" argument(s): "String or binary data would be truncated.
The statement has been terminated."
At line:16 char:1
+ $cmd.ExecuteNonQuery()
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
Figured it out.
$conn = New-Object System.Data.SqlClient.SqlC
$conn.Open()
$cmd = $conn.CreateCommand()
#delete existing contents of this table (enter sqlDB Name below)
$cmd.CommandText = "truncate table " + $dbname+ ".ADGroupMembers"
$cmd.ExecuteNonQuery()
#import new/current data
$content = Get-Content c:\temp\groups.txt
foreach ($Group in $content)
{
$Results = Get-ADGroupMember $Group
Select-Object @{Name="GroupName";Express
foreach ($name in $Results)
{
$insert_stmt = "INSERT INTO ADGroupMembers VALUES('"+ $group +"', '"+ $name.name +"')"
write $insert_stmt
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
}
}
$conn.Close()
$conn = New-Object System.Data.SqlClient.SqlC
$conn.Open()
$cmd = $conn.CreateCommand()
#delete existing contents of this table (enter sqlDB Name below)
$cmd.CommandText = "truncate table " + $dbname+ ".ADGroupMembers"
$cmd.ExecuteNonQuery()
#import new/current data
$content = Get-Content c:\temp\groups.txt
foreach ($Group in $content)
{
$Results = Get-ADGroupMember $Group
Select-Object @{Name="GroupName";Express
foreach ($name in $Results)
{
$insert_stmt = "INSERT INTO ADGroupMembers VALUES('"+ $group +"', '"+ $name.name +"')"
write $insert_stmt
$cmd.CommandText = $insert_stmt
$cmd.ExecuteNonQuery()
}
}
$conn.Close()
Premium Content
You need an Expert Office subscription to comment.Start Free Trial