Teradata Connectivity Using Windows PowerShell

Connectivity
Teradata Employee

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.

10 REPLIES
Teradata Employee

Re: Teradata Connectivity Using Windows PowerShell

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

Teradata Employee

Re: Teradata Connectivity Using Windows PowerShell

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

Teradata Employee

Re: Teradata Connectivity Using Windows PowerShell

Thank you VERY MUCH!

Re: Teradata Connectivity Using Windows PowerShell

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

Teradata Employee

Re: Teradata Connectivity Using Windows PowerShell

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.h...

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

Re: Teradata Connectivity Using Windows PowerShell

Fantastic thanks!

Fan

Re: Teradata Connectivity Using Windows PowerShell

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.

Fan

Re: Teradata Connectivity Using Windows PowerShell

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.

Re: Teradata Connectivity Using Windows PowerShell

hello

I've used the script but i have the following error :

no connection could be made because the target machine actively refused it.

I cheked the firewall but it is not active. do you know how can I fix this problem  ?

Thanks

 

exception.png