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
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.
- This topic was modified 3 months ago by dan-572483.