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

Welcome Forums General PowerShell Q&A I need help with powershell and sql server for generate file base on row value

This topic contains 1 reply, has 2 voices, and was last updated by

 
Participant
5 years, 6 months ago.

  • Author
    Posts
  • #11531

    Participant
    Topics: 1
    Replies: 0
    Points: 0
    Rank: Member

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

  • #11554

    Participant
    Topics: 0
    Replies: 4
    Points: 0
    Rank: Member

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

The topic ‘I need help with powershell and sql server for generate file base on row value’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort