All Forums Connectivity
mhackerott 2 posts Joined 11/11
16 Nov 2011
Teradata Connectivity Using Windows PowerShell

How can a connection to a Teradata database be implemented as a PowerShell script using ODBC, .Net Data Provider, or other protocol.

NetFx 346 posts Joined 09/06
17 Nov 2011
param(  [string] $sqlCommand = $(throw "Please specify a query."),
        [System.Management.Automation.PsCredential] $credential,
        [string] $dataSource = 'YourSystemName')

if ($credential)
{
    $plainCred = $credential.GetNetworkCredential()
    
    $authentication = ("User Id={0};Password={1};" -f $plainCred.Username, $plainCred.Password)
}

Add-Type -assemblyname system.data

$factory = [System.Data.Common.DbProviderFactories]::GetFactory("Teradata.Client.Provider")

$connection = $factory.CreateConnection() 

$connection.ConnectionString = "Data Source = $dataSource;Connection Pooling Timeout=300;$authentication" 
$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = $sqlCommand

$adapter = $factory.CreateDataAdapter()
$adapter.SelectCommand = $command

$dataset = new-object System.Data.DataSet

try
{
    [void] $adapter.Fill($dataset)

    $dataset.Tables | Select-Object -Expand Rows
}
finally
{
    $connection.Close()
}

1- Install the .NET Data Provider for Teradata

2- Save the code to a file; e.g. Invoke-SqlCommand.ps1

2- Change the third Parameter to your system name.

3- Create a PsCredential object: $cred = Get-Credential

4- Execute a SQL Command: .\Invoke-SqlCommand 'HELP SESSION' $cred

 

 

NetFx 346 posts Joined 09/06
17 Nov 2011

I forgot to mention that you can format the output; for example:

1- .\Invoke-SqlCommand 'Select * from DBC.Tables' $cred | format-Table -auto

2- .\Invoke-SqlCommand 'Select * from DBC.Tables' $cred | Out-GridView

 

mhackerott 2 posts Joined 11/11
18 Nov 2011

Thank you VERY MUCH!

brockappleby 3 posts Joined 11/13
26 Oct 2014

Hello this has been very useful thanks!   How might I change this to specify LDAP as the logon mech?  Many thanks

NetFx 346 posts Joined 09/06
27 Oct 2014

You need to modify the Connection String. Refer to this page for a list of valid Connection String attributes: http://developer.teradata.com/doc/connectivity/tdnetdp/15.00/webhelp/webframe.html#DevelopersGuide.html
 

$connection.ConnectionString = "Authentication Mechanism=LDAP;Data Source = $dataSource;Connection Pooling Timeout=300;$authentication" $connection.Open()

 

brockappleby 3 posts Joined 11/13
27 Oct 2014

Fantastic thanks!

sri3@yahoo.com 2 posts Joined 08/15
28 Mar 2016

Greetings! Can you please explain me the step 3 - create a PsCredential object: $cred = Get-Credential. I am new to power shell. I would like to log in via power shell script to a teradata database, run a select query (big), get the data, store it to a text file or csv file. I would appreciate your help. Thank you.
 

sri3@yahoo.com 2 posts Joined 08/15
28 Mar 2016

Greetings! Can you please explain me the step 3 - create a PsCredential object: $cred = Get-Credential. I am new to power shell. I would like to log in via power shell script to a teradata database, run a select query (big), get the data, store it to a text file or csv file. I would appreciate your help. Thank you.
 

You must sign in to leave a comment.