Conversion Issues Upgrading to PowerShell 3.0

This post probably has a small audience, but there were a handful of conversion issues I ran into when I upgraded to PowerShell 3.0. It was difficult finding posts related to the errors I encountered, so I’m posting my experiences in the hopes that it helps someone else down the road.

The first issue I encountered was calling PowerShell from xp_cmdshell on a remote SQL Server.

In PowerShell 2.0, I used this syntax:

EXEC xp_cmdshell 'powershell.exe -Command "get-executionpolicy"'
# PoSh 2.0 Results
# PoSh 3.0 Results
'powershell.exe' is not recognized as an internal or external command,
operable program or batch file.

To fix this issue, I had to fully qualify the PowerShell directory:

EXEC xp_cmdshell 'c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -Command "get-executionpolicy"'
# PoSh 3.0 Results

Another issue I ran into was calling the Teradata .NET Data Provider. In PowerShell 2.0, I was calling the Invoke-SqlStmt function I previously posted to connect from a Windows server to a remote Teradata server.

PS X:\> .\Invoke-SqlStmt.ps1 -C TDATA -S MyTDServerName -Q "SELECT * FROM DBC.Tables;" -Verbose

Everything worked great. However, once I upgraded to PowerShell 3.0, I was getting this error:

Exception calling "GetFactory" with "1" argument(s): "Unable to find the requested .Net Framework Data Provider.  It may not be installed."
At line:1 char:1
+ $Factory = [System.Data.Common.DbProviderFactories]::GetFactory("Teradata.Client ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException

I couldn’t find anything that would explain this behavior, and I confirmed that everything still worked great when ran from PowerShell 2.0. Ultimately, I decided to install the Teradata .NET Data Provider locally, which solved the problem. I didn’t find anything that explained why I could previously use a remote adapter and now can only use a local adapter. I don’t really understand the problem, but I was able to solve it.

Here’s the syntax to install the Teradata .NET Data Provider remotely using xp_cmdshell.

EXEC xp_cmdshell 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -Command "X:\drop\tdnetdp__windows_indep. /s /v"/qn"'

Note that this code is installing the 13.11 adapter, so you may need to change the filename if you’re installing a different version.

And to leave things on a positive note, one awesome improvement of PowerShell 3.0 over 2.0 is the redirection operators. I’m a big fan of logging and verbose messaging, but in 2.0, I had to put extra steps into the code to log these messages. In 3.0, I’ve been able to log my verbose output simply by appending “*> directory” to my commands, i.e.

EXEC xp_cmdshell 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -Command "X:\MyAwesomeScript.ps1 -Verbose *> X:\MyAwesomeLog.txt"'

I think that is just so cool. You can read more about the different redirection operators by running this in PowerShell:

get-help redirect

Happy Scripting! :)