Monthly Archives: August 2013

How To: Capture PowerShell Output in Windows Scheduled Tasks

In a previous blog Capture PowerShell Output in Windows Scheduled Tasks I stated the only reliable way I found to capture PS output is by using a CMD or Bat to execute the PS script. After stumbling upon the correct  way to call PowerShell using a –Command variable, I changed my mind. I found the correct way by reading the PowerShell help in PS v3 (imagine that) which says under –Command:

To write a string that runs a Windows PowerShell command, use the format:
“& {<command>}”
where the quotation marks indicate a string and the invoke operator (&)
causes the command to be executed.

So now, I can call PowerShell from Windows Task Scheduler with the correct syntax while still supplying various parms and send the whole command to an output file. The command would look like this:

Powershell.exe -NonInteractive -NoLogo -NoProfile -Command “& {D:\Scripts\SQL-Servers\Backup-SqlServer.ps1 –Server SQLSERVER -Verbose; Return $LASTEXITCODE}” 2>&1> D:\logs\SQL-Servers\Backup_SQLServer.txt”

This will execute my script, return whatever code I emit from the script, and write any output to a file.

SQL Server 2012 Uses PowerShell V2 Even If It Is On Windows Server 2012

Wow! PS version 3 is great! A host of beneficial changes. If you’re like me, you probably wanted to start using it right away to take advantage of some of the new and wonderful changes.

But, HOLD ON! If you are wanting to implement any of those new and upgraded cmdlets in SQL Server 2012, you can’t. Even if you are running on Windows Server 2012, you still can’t use PS 3 cmdlets. Yes, that’s right. The reason is SQL 2012 uses PS Version 2 internally. Check it out. Here is a screen shot of the $PSVersionTable command in SQL Server 2012 on Windows Server 2012 (after right-clicking a database and selecting Start PowerShell):

Here’s a screen shot of $PSVersionTable in Powershell on the same server

So, If you want to run PS 3 for any job steps on SQL 2012, you will need to choose the command type as Operating System (CmdExec) and not PowerShell.

Use Powershell to Copy Database Table Data

Note: Requires PS version 3.0 and must import module SQLPS

I had been using SSIS to copy some database tables between different servers. However, I really like Powershell and I thought it might be a little easier to control things when things change (like servers, table columns, etc.)

I found a really good article with a great code example here at Hey, Scripting Guy. For most people, the code will work “out of the box”. However, I did run into a snag on one of my tables. It turned out the table had two computed columns and SqlBulkCopy did not like that at all!

After a little research I found I could still use the “bulk” of the code (snicker) with some adjustments. To eliminate references to the computed columns I had to build the SELECT using column names vs. the out-of-the-box SELECT *.  This was easily accomplished be getting all the column names and then using the –join operator to construct a list of comma separated names. Here’s the piece of code to do just that:

$cols = @(Get-ChildItem SQLSERVER:SQL\$Server\databases\$SrcDatabase\Tables\$SrcTable\Columns |
		Where-Object -Property Computed -NE $true | Sort-Object -Property ID |
		ForEach-Object{"[" + $_.Name + "]"})
	$colnames = $cols -join ","

The other change I needed was to add column mapping to the SqlBulkCopy. That was another piece of easy code. However, it is only easy if the column names are identical between tables. In my case they are. And, I already have an array of column names from the code I used above.

# Map the columns
Foreach($col in $cols)
    [Void]$bulkCopy.ColumnMappings.Add($col, $col)

That is the extent of changes. All credit really goes to the Hey, Scripting Guy Blog! Here is the entire source if you need to borrow it.

	Copies data from a database table to another database.
	Copy the data from a source table to a destination table. This script uses 
	the .NET SqlBulkCopy class with a streaming IDataReader to achieve the 
	optimal performance (using a single thread).
.Parameter SrcServer
	The name of the source SQL server instance.
.Parameter SrcDatabase
	The name of the source databases.
.Parameter SrcTable
	The name of the source table to copy data from.
.Parameter DestServer
	The name of the destination SQL Server instance
.Parameter DestDatabase
	Name of the destination database. When omitted, it is set to the source
.Parameter DestTable
	Name of the destination table. When omitted, it is set to the source
.Parameter Truncate
	When this switch is included, the destination table is truncated prior to 
	AUTHOR:		John P. Wood
	Company:	ATOS Origin\Lee County BOCC
	CREATED:	August, 2013
	VERSION:	1.0.0	
#requires -version 3.0
Param (
      [parameter(Mandatory = $true)] 
      [string] $SrcServer,
      [parameter(Mandatory = $true)] 
      [string] $SrcDatabase,
      [parameter(Mandatory = $true)] 
      [string] $SrcTable,
      [parameter(Mandatory = $true)] 
      [string] $DestServer,
      [string] $DestDatabase, 
      [string] $DestTable, 
      [switch] $Truncate 
Function ConnectionString([string] $ServerName, [string] $DbName) 
	"Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;"

If ($DestDatabase.Length –eq 0) 
	$DestDatabase = $SrcDatabase

If ($DestTable.Length –eq 0) 
	$DestTable = $SrcTable

	$SrcConnStr = ConnectionString $SrcServer $SrcDatabase
	$SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)
	# Build SELECT with discreet column names due to possibilty of computed 
	# columns which cannot be bulk copied
	$server = $SrcServer
		$server = "$SrcServer\default"
	$cols = @(Get-ChildItem SQLSERVER:SQL\$Server\databases\$SrcDatabase\Tables\$SrcTable\Columns |
		Where-Object -Property Computed -NE $true | Sort-Object -Property ID |
		ForEach-Object{"[" + $_.Name + "]"})
	$colnames = $cols -join ","
	$CmdText = "SELECT $colnames FROM " + $SrcTable
	$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)  
	[System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()
	If ($Truncate) 
		$TruncateSql = "TRUNCATE TABLE " + $DestTable
		Sqlcmd -S $DestServer -d $DestDatabase -Q $TruncateSql

	$DestConnStr = ConnectionString $DestServer $DestDatabase
	$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, `
#	$bulkCopy.BatchSize = 1000
	$bulkCopy.BulkCopyTimeout = 0

	$bulkCopy.DestinationTableName = $DestTable
	# Map the columns
	Foreach($col in $cols)
		[Void]$bulkCopy.ColumnMappings.Add($col, $col)
	Write-Output "$SrcTable in $SrcDatabase on $SrcServer"
	Write-Output "`tcopied to $DestTable in $DestDatabase on $DestServer"
	$ex = $_.Exception
	Write-Error $ex.Message
		$ex = $ex.InnerException
		Write-Error $ex.Message