Using Powershell to run a script to output a script

Question

Using Powershell to run a script to output a script

dan-572483

SSCoach

Points: 15870
More actions
May 5, 2020 at 11:05 pm

#3749110
I am building a SQL Agent job to automate refreshing a nonproduction database from a backup of Production. One of the steps in this process is to run a vendor-supplied script that reads environment-specific configuration values and creates an update script that is run after the database restore to set these values to the nonproduction values. When running this manually in SSMS, I copy the results to a new window, save as an SQL file and it works great.
But how do I automate this so I have a saved script to run after the restore? The approach I've taken is to put a Powershell step in the agent job to run an invoke-SqlCmd command to run the script and save the output. However, using Out-file just puts the Results Tab into the file, and "The command completed successfully" doesn't do me any good.
After searching on Google, I found the following syntax, which appears to work:
```
invoke-SqlCmd -InputFile "G:\filepath\ScriptOutEnvironmentVariables.sql" -ServerInstance MyInstance -Database MyDatabase -Verbose 4> "C:\temp\outputscript.sql"
```
This appears to create a sql file I can run in the step following the database restore. But we have another issue.
When Powershell creates the update script, it inserts carriage returns on each line after a blank space after about 100 characters. When it does this between keywords in commands there's no problems, but I'm getting one inside a filepath that is being the command. So
```
UPDATE ConfigTable SET ConfigValue = 'D:\Program Files\ApplicationName\Application Folder\temp' where ConfigTableID = 123
```
becomes
```
UPDATE ConfigTable SET ConfigValue = 'D:\Program Files\ApplicationName\Application <carriage return>
 Folder\temp' where ConfigTableID = 123
```
The carriage control characters are written into the path value in the database, which causes the application to throw errors.
Oddly when I run the same Powershell command on my workstation, the carriage returns are father out on the line, and the problem update command is correct. This makes me think this is due to some OS or Powershell config setting I'm not aware of.
Any ideas?
- This topic was modified 4 years ago by dan-572483.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I'm sure there's a PowerShell command that will remove carriage return characters from a string. Failing that, you can do it in T-SQL with STUFF or REPLACE.

John

Lowell SSC Guru Points: 323506 More actions · Answer 2

Dan, rather than use the > redirect, I've been using | Out-File with the Append parameter, and it works flawlessly for me: i return info messages, data sets etc, dividers, notes and even error messages with my scripts;

In my case, I am executing all SQL scripts in a folder, but the parameters might help, si I am including the whole example:

###################################################################################################
## Loop through each file in current folder, and also each sub folder
###################################################################################################
#$SQLServer = $env:COMPUTERNAME ##the local machine / default MSSQLSERVER?
#$SQLServer = '(LocalDB)\WIN-05LQX24A-Prod'
$SQLServer = 'StormNuc'
$SQLDatabase = "master"
$BasePath ="D:\Data\StandardTakeOver\AutomatedInstall"

$LogPath = [System.IO.Path]::Combine($BasePath,"SQLScriptLogs_" + (Get-Date).ToString("yyyy-MM-dd") + ".txt")
#$LogPath = [System.IO.Path]::Combine($BasePath,"ScriptResults.txt")
Clear-Host
if($SQLServer -ne '' -and [System.IO.Directory]::Exists($BasePath))
{
###################################################################################################
## any files in current folder?
###################################################################################################
     $AllSQLFiles =  [System.IO.Directory]::GetFiles($BasePath,'*.sql') | Sort-Object
    foreach($sqlfile in $AllSQLFiles)
        {
            try
            {
                $StagingFileName = "--Begin " + [System.IO.Path]::GetFileName($sqlfile)
                Write-Host $StagingFileName -ForegroundColor Green
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
                Invoke-SqlCmd -InputFile $sqlfile  -ServerInstance $SQLServer -Database $SQLDatabase -Querytimeout 600 | Out-File -LiteralPath $LogPath -Append -Encoding ASCII -Width 99999
                $StagingFileName = "--End   " + [System.IO.Path]::GetFileName($sqlfile)
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999

            }
            catch
            {
                $ErrorMessage = $_.Exception.Message
                $FailedItem = $_.Exception.ItemName
                $StagingFileName = "--Error " + [System.IO.Path]::GetFileName($sqlfile)
                Out-File -LiteralPath $LogPath -InputObject $FailedItem -Append -Encoding ASCII -Width 99999
                Out-File -LiteralPath $LogPath -InputObject $ErrorMessage -Append -Encoding ASCII -Width 99999
                Out-File -LiteralPath $LogPath -InputObject $StagingFileName -Append -Encoding ASCII -Width 99999
            }

        }

This reply was modified 4 years ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!