Author Posts

November 16, 2013 at 12:25 pm

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()

November 17, 2013 at 4:50 pm

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)
}