0

I have a Powershell script that runs some sql files to install tables/stored procedures. Within the PS script I output some text to the log file before each file is run, then perform an Invoke-SqlCmd using -Verbose for the sql file and redirect all output to the same log file. This all works great, and the output from the PS script comes out looking normal, but any output from the sql file (via PRINT or from an error in the DDL) comes out with the letters double-spaced apart. Here is a sample of the output from the log file:

--- Deploying MyTable ----

--- Deploying MyTable.sql...

--- D r o p p i n g t a b l e d b o . M Y T A B L E

--- T a b l e d b o . M Y T A B L E s u c c e s s f u l l y c r e a t e d

Here is the code I am running:

Clear-Host
$sqlServer = Read-Host -Prompt "Enter Server"
$sqlServer
$db = Read-Host -Prompt "Enter Database"
$db
$logFolder = Read-Host -Prompt "Enter log file folder"
$logFolder

$logFileName = "MyTable.txt"

rm $logFolder\$logFileName -ErrorAction Ignore

Add-Content $logFolder\$logFileName "#############################################"
Add-Content $logFolder\$logFileName "### Deploying MyTable                    ####"
Add-Content $logFolder\$logFileName "#############################################"
Add-Content $logFolder\$logFileName " "

Add-Content $logFolder\$logFileName "Deploying MyTable.sql..."
Invoke-Sqlcmd -ServerInstance $sqlServer -Database $db -InputFile "MyTable.sql" -Verbose *>> $logFolder\$logFileName

Everything works fine, but looks terrible. Any thoughts on how I might resolve the formatting of the output from the PRINT statements and the error messages from the sql?

Dan
  • 1
  • Is it still the same if you use `... | Add-Content` rather than `*>>`? – Ash May 14 '20 at 12:27
  • Some of the content is in double-byte encoding, the rest is not. Convert the UTF16 into UTF8 or ASCII, so extra zero bytes are not represented as e x t r a s p a c i n g . – vonPryz May 14 '20 at 13:12
  • @Ash - I could not get Add-Content to behave nicely output from Invoke-SqlCmd, not sure why. It did find the solution though. Thanks for the help! – Dan May 18 '20 at 11:07
  • @vonPryz - This was the answer, thanks! I found the default setting in PS, changed it, and it worked like a charm! – Dan May 18 '20 at 11:08
  • @Dan You can write and accept your own answer to the question. Please do, so if someone finds the question, there's a nice answer too. – vonPryz May 18 '20 at 13:15

0 Answers0