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 AllSigned # 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 AllSigned
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.13.11.00.01.exe /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: