So I’ve talked before about keeping new lines when copying results to a different window in SQL Server and about copying new lines out of SQL Server into reports.
These topics have come about as they are both issues that I’ve had to deal with. Well, another of those issues is dealing with exporting special characters out of SQL Server using PowerShell.
The Lay-out.
We already have our table called “dbo.NewLineNotes” from before when we were trying to copy new lines out of SQL Server so we’re going to add another row.
Now personal experience for me centered around the German Eszett (“ß”) but you may encounter this with other characters.
-- Insert some special characters... INSERT INTO dbo.NewLineNotes (Notes) VALUES (N'This is a ß')
Now if you were to use the code from keeping new lines post…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes' | |
$InvSQLParams = @{ | |
ServerInstance = '0738-5CG140G7T\SQLSERVER2K16' | |
Database = 'Pantheon' | |
Query = $NewLineQuery | |
} | |
Invoke-Sqlcmd @InvSQLParams | | |
Select-Object –Property Notes | | |
Export-Csv –Path 'C:\Users\soneill\Desktop\In Progress\Test\NewLines.csv' –NoTypeInformation |
and open up the csv file we would get…

“What do we do when we fall down?”…
Well with SQL Server, I normally break things down into the smallest parts and slowly build it up until it breaks. For this, it breaks when we get to Export-CSV
as everything before it works!

PowerShell is even easier for troubleshooting methodology as , and we’ve talked about it before, Get-Member
and Get-Help
are there to help us!
We know that it’s Export-CSV
that is somehow screwing up our special character so the obvious next step…
help Export-CSV -Full;
And we can see a parameter just shine at us!

So we have to define an “Encoding” do we? I used “UTF8” and modified my query…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$NewLineQuery = 'SELECT Notes FROM dbo.NewLineNotes' | |
$InvSQLParams = @{ | |
ServerInstance = '0738-5CG140G7T\SQLSERVER2K16' | |
Database = 'Pantheon' | |
Query = $NewLineQuery | |
} | |
Invoke-Sqlcmd @InvSQLParams | | |
Select-Object –Property Notes | | |
Export-Csv –Path 'C:\Users\soneill\Desktop\In Progress\Test\NewLines.csv' –NoTypeInformation –Encoding UTF8; |

And special characters are no longer an issue for us 🙂