Using Powershell to run a script to output a script

  • dan-572483

    SSCoach

    Points: 15865

    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 3 months ago by  dan-572483.
  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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: 323459

    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 3 months 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!

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

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