powershell breaking my string
<p>Powershell breaks my strings when I have specifically told it to put the carriage return at the end of the line</p>

<p>I have PS script that is supposed to write a batch sql insert in groups of 100:</p>

<pre><code>function WriteBatch {
    echo $sql
    $Command.CommandText = $sql
        $Command.ExecuteNonQuery()
}

$server = "norcrpsqldev08"
$Database = "SandBoxVinny"
$Path = "Y:\BMPS"
$Counter = 0

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
[string]$sql =     "
                begin 
                    insert into TestPowerShell(NameString, FileSize, Mode, Directory, CreationTime, LastAccessTime, LastWriteTime)
                    values " 

foreach($file in Get-ChildItem -Verbose -Recurse -Path $Path | Select-Object  Name, Length, Mode, Directory, CreationTime, LastAccessTime, LastWriteTime) {
    $fileName = $file.Name
    $fileSize = ([int]$file.Length)
    $fileMode = $file.Mode
    $fileDirectory = $file.Directory
    $fileCreationTime = [datetime]$file.CreationTime
    $fileLastAccessTime = [datetime]$file.LastAccessTime
    $fileLastWriteTime = [datetime]$file.LastWriteTime
    $sql = $sql + "('$fileName', '$fileSize', '$fileMode', '$fileDirectory', '$fileCreationTime', '$fileLastAccessTime', '$fileLastWriteTime'),`r`n"
    #$sql += "`n"
    $Counter++

    If($Counter -eq 100) {
        $sql = $sql.Trim().Trim(',')
        $sql = $sql + " End"
        WriteBatch
        $Counter = 0
        $sql = "
                begin 
                    insert into TestPowerShell(NameString, FileSize, Mode, Directory, CreationTime, LastAccessTime, LastWriteTime)
                    values " 
    }

}

if ($Counter -gt 0){
    $sql = $sql.Trim().Trim(',')
    $sql = $sql + " End"
    WriteBatch
}

$Connection.Close()
</code></pre>

<p>The resulting strings often have a line break where it is not supposed to be:</p>

<p>Results:</p>

<pre><code>    ('PCA - implementation demand post transfer 9.14.16.xlsx', '56191', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:46:30', '09/14/2016 17:52:08', '09/14/2016 17:52:08'),
('RCA - implementation demand post transfer 9.14.16_bad.xlsx', '55442', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:46:30', '09/14/2016 17:46:55', '09/14/2016 17:46:55'
),
('RCA System - post transfer 9.14.16 No account.xlsx', '40498', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:20:37', '09/14/2016 17:26:49', '09/14/2016 17:26:49'),
</code></pre>

<p>This should look like this:</p>

<pre><code>    ('PCA - implementation demand post transfer 9.14.16.xlsx', '56191', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:46:30', '09/14/2016 17:52:08', '09/14/2016 17:52:08'),
('RCA - implementation demand post transfer 9.14.16_bad.xlsx', '55442', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:46:30', '09/14/2016 17:46:55', '09/14/2016 17:46:55'),
('RCA System - post transfer 9.14.16 No account.xlsx', '40498', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:20:37', '09/14/2016 17:26:49', '09/14/2016 17:26:49'),
</code></pre>

<p>This breaks my sql.  Any thoughts?</p>