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.
Like. Looking forward to some powershell screenshots.
Pingback: Something for the Weekend - SQL Server Links 22/02/13
Uh … Yeah …. I’m stealing this script.
Pingback: Conversion Issues Upgrading to PowerShell 3.0 SQL Fool
Pingback: Conversion Issues Upgrading to PowerShell 3.0 - SQL Server Blog - SQL Server - Telligent
{ $ConnectionString = “Server={0};Database={1};Integrated Security=True;Connect Timeout={2}” -f $S,$D,$CT }
Do you mean $T where you have $CT? Also, I’m looking for a new editor and was curious what you use. Thanks.
System.Void argument error is showing up, can you help me to find out what is happening?
thanks
This is exactly what I am looking for.. Thank you Michelle…
Michelle – As Rob pointed out there is a typo in the script $CT should be replaced with $T..
How would I tell Teradata .Net Provider to use LDAP?
The standard connection string “Authentication=LDAP;…etc” doesn’t work 🙁
and fantastic script thanks for sharing!
System.Void argument error is showing up.. can you help me with this error?
@miguel i ran into the same issue as you with the system.void data type. I went into the scripts and remove the data type casting [void] and that resolved the issue. It should be line 95 and 136. I’m not sure why this works on ps2 and not on ps3+. Hope that helps.
To fix the System.Void argument issue you just remove the parts with [Void] for the datasets.