I need help with powershell and sql server for generate file base on row value

PowerShell Help
1

Hello Dears,
first sorry I new bee to powershell and I have to realize this script for my job.
on a sql server database, I have a table with 3 columns ( ClientId,ReportId,Details). I have to generate for each rown of my table a file containing the value of the column Details,the file generate has to be name with the value of ReportId and store in a local folder having for name the value of ClientId.
Exple my table ClientID,ReportId,Details
58001 1040 {\rtf1\ans} I will on my C:\RootFolder\58001\1040.rtf for exple…
ps: that column Details store native RTF coding, therefore i need to generate my file with rtf extension.
and my table has over 200’000 rows…
Please help.
here is what i have started, but really difficult to understand it all…
$ServerConnectionString = “Data Source=UK138032\VASCO;Initial Catalog=CMTLite;trusted_connection=true;”
$ServerConnection = new-object system.data.SqlClient.SqlConnection($ServerConnectionString);

$dataSet = new-object “System.Data.DataSet” “Details”
$query = “SELECT DISTINCT [ClientId] FROM [CompanyDelta].[dbo].[Orders] where ClientId”

$dataAdapter = new-object “System.Data.SqlClient.SqlDataAdapter” ($query, $ServerConnection)
$dataAdapter.Fill($dataSet) | Out-Null

$ServerAConnection.Close()

$dataTable = new-object “System.Data.DataSet” “ReportId”
$dataTable = $dataSet.Tables[0]

$ServerConnectionString = “Data Source=UK138032\VASCO;Initial Catalog=CMTLite;trusted_connection=true;”
$ServerConnection = new-object system.data.SqlClient.SqlConnection($ServerConnectionString);
$ServerConnection.Open()

$dataTable | FOREACH-OBJECT {
$cmd = new-object System.Data.SQLClient.SQLCommand
$cmd.CommandText = “SELECT [Details] FROM [CompanyDelta].[dbo].[Orders] where ClientId= @ClientId”
$cmd.Connection = $ServerConnection
$cmd.Parameters.AddWithValue(“@ClientId”, $_.Clientid)
$rowsUpdated = $cmd.ExecuteNonQuery()

}

$ServerBConnection.Close()

2

I didn’t read through the exact query, but you would most likely want to create a custom object collection (named ‘$report’ below). Then the below should work:

$rootDirectory = "c:\temp"
$report | ForEach-Object {
if (! (Test-Path $($rootDirectory + $.ClientId) ) { Create-Item -Path $($rootDirectory + $.ClientId }
if (! (Test-Path $($rootDirectory + $.ClientId + "" + $.ReportId) ) { Create-Item -Path $($rootDirectory + $.ClientId + "" + $.ReportId }
$.Details | Out-File -Path $($rootDirectory + $.ClientId + "" + $.ReportId + "" + $.Details)
}