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
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:

get-help redirect

Happy Scripting! :)

Invoke-SqlStmt – PowerShell for SQL Server & Teradata Queries

I’ve been using PowerShell quite a bit the last few months, and I finally feel I’m to the point where I can start sharing some of what I’ve learned.

I started a blog post that walks through using PowerShell for database development and automation, and I quickly realized that a lot of what I want to show is dependent upon the first function I wrote, Invoke-SqlStmt. And by “wrote,” I mean “blatantly borrowed from Chad Miller’s Invoke-SqlCmd2 script;) . Thank you, Chad! The main difference is that my function supports connecting to either Teradata or SQL Server, depending on the parameters passed.

One thing worth mentioning is that I spent a lot of time researching all of the different ways to connect to SQL Server. In the end, I went with ADO.NET, as a lot of what I will be doing is working with data sets (i.e. running a query against ServerA and writing the results to ServerB).

Invoke-SqlStmt

##################################################################################################
#
#   .NAME
#       Invoke-SqlStmt.ps1
#
#   .SYNOPSIS
#       Executes a SQL statement against either a Teradata or SQL Server source using ADO.NET
#
#   .INPUT
#       C - Connection Type; Required; MSSQL or TDATA
#       S - Server Name; Required; server name for any valid SQL Server or data instance
#       Q - Query; Optional; either Q or F is required
#       F - Filename; Optional; either Q or F is required
#       U - Username; Optional; if not supplied, Integrated mode is assumed
#       P - Password; Optional; if not supplied, Integrated mode is assumed
#       T - Connection Timeout; Optional; defaults to 600s but can be overridden
#       AS - Optional; can specify whether DataSet, DataTable, or DataRow will be returned
#
#   .OUTPUTS
#       System.Data.(DataSet|DataTable|DataRow)
#
#   .CREDIT
#       Modified from Chad Miller's Invoke-SqlCmd2 script:
#       http://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894#content
#
#  --------------------------------------------------------------------------------------------
#
#   .EXAMPLES
#
#   Invoke-SqlStmt -C MSSQL -S "(local)" -Q "SELECT @@SERVERNAME;"
#     This example connects to a named instance of the D Engine on a computer and runs a basic T-SQL Q.
#
#   Invoke-SqlStmt -C MSSQL -S "(local)" -F "C:\myScript.sql" | Out-File -filePath "C:\myOutput.txt"
#     This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
#
#   Invoke-SqlStmt  -C MSSQL -S "(local)" -Q "PRINT 'hello world'" -Verbose
#     This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
#
#   Invoke-SqlStmt  -C TDATA -S "(local)" -Q "SELECT * FROM DBC.Tables;"
#     This example connects to Teradata and executes a query. 
#
#  --------------------------------------------------------------------------------------------
#
#   AUTHOR:     Michelle Ufford
#   CREATED:    2012-12-12
#
#  CHANGE HISTORY:
#  --------------------------------------------------------------------------------------------
#  ChangeDate/Version   Author                  Description
#  --------------------------------------------------------------------------------------------
#  20121212.0           Michelle Ufford         Initial Creation
#  20121218.1           Michelle Ufford         Tweaks
##################################################################################################
 
function Invoke-SqlStmt
{
    [CmdletBinding()]
    param(
    [Parameter(Position=0, Mandatory=$false)] [ValidateSet("MSSQL", "TDATA")] [string]$C="MSSQL",
    [Parameter(Position=1, Mandatory=$true)]  [string]$S,
    [Parameter(Position=2, Mandatory=$false)] [string]$D,
    [Parameter(Position=3, Mandatory=$false)] [string]$Q,
    [Parameter(Position=4, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$F,
    [Parameter(Position=5, Mandatory=$false)] [string]$U,
    [Parameter(Position=6, Mandatory=$false)] [string]$P,
    [Parameter(Position=8, Mandatory=$false)] [Int32]$T=600,
    [Parameter(Position=9, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$AS="DataRow"
    )
 
    # Check for input files
    if ($F)
    {
        $filePath = $(resolve-path $F).path
        $Q =  [System.IO.File]::ReadAllText("$filePath")
    }
 
    # ----------------------
    # Teradata Connections
    # ----------------------
    if ($C -eq "TDATA")
    {
        $ConnectionString = "Data Source={0}; User Id={1}; Password={2}; Connection Pooling Timeout={3};" -f $S, $U, $P, $T
 
        Add-Type -AssemblyName System.Data
        $Factory = [System.Data.Common.DbProviderFactories]::GetFactory("Teradata.Client.Provider")
        $Connection = $Factory.CreateConnection()
        $Connection.ConnectionString = $ConnectionString
        $Connection.Open()
        $Command = $Connection.CreateCommand()
        $Command.CommandText = $Q
        $DataSet = New-Object System.Data.DataSet
        $DataAdapter = $Factory.CreateDataAdapter()
        $DataAdapter.SelectCommand = $Command
        $DataAdapter.SelectCommand.CommandTimeout = $T
        [void] $DataAdapter.Fill($DataSet) | Out-Null
        $Connection.Close()
 
        # get a table contained in the DataSet, http://technet.microsoft.com/en-us/library/dd347701.aspx
        switch ($As)
        {
              'DataSet'   { Write-Output ($DataSet) }
              'DataTable' { Write-Output ($DataSet.Tables) }
              'DataRow'   { Write-Output ($DataSet.Tables[0]) }
        }
    }
    else
    # ------------------------
    # SQL Server Connections
    # ------------------------
    {
        $Connection = New-Object System.Data.SqlClient.SQLConnection
 
        if ($U)
        { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $S,$D,$U,$P,$T }
        else
        { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $S,$D,$CT }
 
        $Connection.ConnectionString=$ConnectionString
 
        #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
        if ($PSBoundParameters.Verbose)
        {
            $Connection.FireInfoMessageEventOnUserErrors=$true
            $Handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
            $Connection.add_InfoMessage($Handler)
        }
 
        $Connection.Open()
        $Command = New-Object System.Data.SqlClient.SqlCommand
        $Command.Connection = $Connection
        $Command.CommandText = $Q
        $Command.CommandTimeout = $T
        $DataSet = New-Object System.Data.DataSet
        $DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $DataAdapter.SelectCommand = $Command
        [void]$DataAdapter.Fill($DataSet) | Out-Null
        $Connection.Close()
 
        switch ($As)
        {
            'DataSet'   { Write-Output ($DataSet) }
            'DataTable' { Write-Output ($DataSet.Tables) }
            'DataRow'   { Write-Output ($DataSet.Tables[0]) }
        }
    }
}

I plan to have a follow-up post up shortly that will walk you through how to use this script step-by-step.

The SQL Server DBA’s Guide to Teradata

6 months ago, I returned from maternity leave only to be handed the proverbial keys to a new database kingdom: Teradata. Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on this new environment. In an effort to save others from the same headaches that plagued me the first few months, I have created a new guide to aid those new to the Teradata platform. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. Rather than create a series of posts with random tidbits, I thought it would make more sense to create a single page for this guide and update it incrementally. You can find the very beginnings of this guide here: The SQL Server DBA’s Guide to Teradata. Good luck, and happy converting. :)

A question for my blog readers…

My role at work has recently changed. While I will continue to be working with SQL Server, I will also be spending a good chunk of my time working in our newly acquired Teradata environment. My plan is to blog about both as time permits, but I am trying to determine whether I should create a separate blog for the Teradata content. So my question for you, dear reader, is whether you think I should blog on this site about both SQL Server *and* Teradata, or whether I should separate the content? Any input is appreciated.