I have below PS that grabs data from AD and puts it in a CSV file. Then it should insert the data to the SQL server and overwrite existing data.
For some reason I just can't get this to work. Spent the last 5 hours searching here and other places but to no avail.
Get-ADGroupMember -Identity "CN=Nearmiss APP,CN=Users,DC=domain,DC=AD" -Recursive |
Get-ADUser -Properties Mail,displayName |
Select-Object displayName,Mail |
Export-CSV -Path D:\autostore\GetADusers\NearMissApp.csv -NoTypeInformation -encoding "unicode" -force
# (Get-Content D:\autostore\GetADusers\NearMissApp-Temp.csv) | % {$_ -replace ‘"‘, ''} | out-file D:\autostore\GetADusers\NearMissApp.csv -Force
#Connect to the SQL database NearMissApp-Users
$sqlsvr = "sql"
$database = "autostore"
$table = "NearMissApp-Users"
$csvFile = "D:\autostore\GetADusers\NearMissApp.csv"
#Create SQL Connection
$cmd.Connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsvr;Initial Catalog=$database; Integrated Security=true")
$cmd.command = $connection.CreateCommand()
$cmd.Connection.Open()
Import-Csv $csvFile -Header displayName,Mail | % {
$cmd.CommandText = "INSERT INTO $table (displayName, Mail) VALUES ('$($_.displayName)', '$($_.Mail)')" | Out-host
$cmd.ExecuteNonQuery()
}
$cmd.connection.close
The error I get is:
Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery: CommandText property has not been initialized" At D:\autostore\GetADusers\GetUsers-NearMiss-App-DBupdate.ps1:23 char:3 + $cmd.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : InvalidOperationException
