Joel Greijer

When importing sqlps module implicitly you sometimes get the prompt changed to sqlserver:> in your powershell session. This can be hacked by explicitly loading sqlps in your script. Since this hogs your verbosity log you have the option to disable it with the output stream redirection hack used below. This script uses a defined path which is neccessary if running on teamteamcity where sqlps is not installed. # begin hack because in some cases invoke-sql is bugged, leaving prompt hanging in sqlserver:> try { write-verbose "Importing sqlps" $psmodulepath = join-path $PsScriptRoot MsBuild\Powershell\Modules\SQLPS Push-Location Import-Module -name $psmodulepath -DisableNameChecking 4>$null; #use line below to load where sqlps exists on executing machine: #Import-Module sqlps -DisableNameChecking 4>$null; Pop-Location #reset invalid prompt location } catch { write-error ( "There was an error importing sqlps: {0}" -f $_ .Except...

There are some hoops you need to jump through to get a multi-statement transaction committed via powershell. Default time-out is set to only one minute, so using the code below you can increase it. $timeout = New-Object System.TimeSpan -ArgumentList @ (5,0,0) # five hours $options = [System.Transactions.TransactionScopeOption] :: Required $scope = New-Object -TypeName System.Transactions.TransactionScope -ArgumentList @ ( $options , $timeout ) try { #Multiple queries, loops or whatever fun here, inside transaction Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName1 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName2 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName3 ; $scope .Complete() #we tell transactionscope to commit when using e...

Problem 1 I tried adding a powershell script to a sql server agent job step, but there are some severe limitations where write-host or write-verbose outputs are not allowed instead of ignored, which should make sens. Then the job crashes with the following error:  A job step received an error at line [xx] in a PowerShell script. The corresponding line is 'write-host "test";  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows.  '.  Process Exit Code -1.  The step failed. Problem 2 So instead I created a Operating System (CmdExec) step with calling pow...