Hi,

I'm using get-QADuser to query AD, then I am writing those results to a local DB.  However, when I write the memberOf attribute to the DB, I find that it only writes sytemString[] which I believe is because ADSI stores this data as an array??

Could anyone point me in the right direction of how to convert this into something I could write to a MSSQL DB?

Here is basically what I am using - I am using more ADSI attributes, but it was confusing when I pasted into this post.
----
invoke-expression sqlcmd

Get-qaduser xxxxx -IncludeAllProperties -SizeLimit 0 | select samaccountname,memberOf | foreach {

 $sql= "INSERT INTO robdb.dbo.robdb.dbo.Test_tbl (samaccountname,memberOf) VALUES ( '{0}','{1}' )" -f $_.SamAccountName,$_.memberof
 
 invoke-sqlcmd $sql
}
----
Thanks

Rob.

You need to join the value to one string:

$sql= "INSERT INTO robdb.dbo.robdb.dbo.Test_tbl (samaccountname,memberOf) VALUES ( '{0}','{1}' )" -f $_.SamAccountName,[string]::join(";",$_.memberof)

With PowerShell 2.0 you can use the -join operator:

$sql= "INSERT INTO robdb.dbo.robdb.dbo.Test_tbl (samaccountname,memberOf) VALUES ( '{0}','{1}' )" -f $_.SamAccountName, ($_.memberof -join ";")

Hi agian,

Thanks for the response. It has worked - however, I'm now finding that the Insert is attempting to write more prefixes to the table than allowed - giving the error - The object name 'robdb.dbo.robdb.dbo.Test_tbl' contains more than the maximum number of prefixes. The maximum is 3.

I've tried a replace function in the SQL, which gave the same response

$sql= "INSERT INTO robdb.dbo.robdb.dbo.Test_tbl (samaccountname,replace(memberOf,''',''',' ') VALUES ( '{0}','{1}' )" -f $_.SamAccountName, ($_.memberof -join ";")

write-host $sql

invoke-sqlcmd $sql

Now I wonder is it possible to remove the quote marks in the memberOf array so that the SQL can insert it as needed?

Thanks again

Rob

Using PSv2

Try with PowerShell's replace operator:

$sql= "INSERT INTO robdb.dbo.robdb.dbo.Test_tbl (samaccountname,memberOf) VALUES ( '{0}','{1}' )" -f $_.SamAccountName, (($_.memberof -join ";") -replace "'"," ")

Shay, you are a legend. Thank you very much, it is much appreciated.

Merry Christmas.

Robbie.