PoshCode Logo PowerShell Code Repository

Invoke-SqlCmd2 by Justin Dearing 21 months ago (modification of post by Chad Miller view diff)
View followups from Atamido | diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2950"></script>download | new post

Modeled after SQL Server 2008 Invoke-Sqlcmd, but fixes bug in QueryTimeout, and allows for paramaterized queries.

  1. #######################
  2. <#
  3. .SYNOPSIS
  4. Runs a T-SQL script.
  5. .DESCRIPTION
  6. Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified.
  7. Paramaterized queries are supported.
  8. .INPUTS
  9. None
  10.     You cannot pipe objects to Invoke-Sqlcmd2
  11. .OUTPUTS
  12.    System.Data.DataTable
  13. .EXAMPLE
  14. Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
  15. This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
  16. StartTime
  17. -----------
  18. 2010-08-12 21:21:03.593
  19. .EXAMPLE
  20. Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
  21. This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
  22. .EXAMPLE
  23. Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
  24. This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
  25. VERBOSE: hello world
  26. .NOTES
  27. Version History
  28. v1.0   - Chad Miller - Initial release
  29. v1.1   - Chad Miller - Fixed Issue with connection closing
  30. v1.2   - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
  31. v1.3   - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
  32. v1.4   - Justin Dearing <zippy1981 _at_ gmail.com> - Added the ability to pass parameters to the query.
  33. #>
  34. function Invoke-Sqlcmd2
  35. {
  36.     [CmdletBinding()]
  37.     param(
  38.     [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
  39.     [Parameter(Position=1, Mandatory=$false)] [string]$Database,
  40.     [Parameter(Position=2, Mandatory=$false)] [string]$Query,
  41.     [Parameter(Position=3, Mandatory=$false)] [string]$Username,
  42.     [Parameter(Position=4, Mandatory=$false)] [string]$Password,
  43.     [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
  44.     [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
  45.     [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
  46.     [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow" ,
  47.     [Parameter(Position=9, Mandatory=$false)] [System.Collections.IDictionary]$SqlParameters
  48.     )
  49.  
  50.     if ($InputFile)
  51.     {
  52.         $filePath = $(resolve-path $InputFile).path
  53.         $Query =  [System.IO.File]::ReadAllText("$filePath")
  54.     }
  55.  
  56.     $conn=new-object System.Data.SqlClient.SQLConnection
  57.      
  58.     if ($Username)
  59.     { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
  60.     else
  61.     { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
  62.  
  63.     $conn.ConnectionString=$ConnectionString
  64.      
  65.     #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
  66.     if ($PSBoundParameters.Verbose)
  67.     {
  68.         $conn.FireInfoMessageEventOnUserErrors=$true
  69.         $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
  70.         $conn.add_InfoMessage($handler)
  71.     }
  72.      
  73.     $conn.Open()
  74.     $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
  75.     $cmd.CommandTimeout=$QueryTimeout
  76.     if ($SqlParameters -ne $null)
  77.     { $SqlParameters.GetEnumerator() | ForEach-Object { $cmd.Parameters.AddWithValue($_.Key, $_.Value)  } > $null }
  78.      
  79.     $ds=New-Object system.Data.DataSet
  80.     $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
  81.     [void]$da.fill($ds)
  82.     $conn.Close()
  83.     switch ($As)
  84.     {
  85.         'DataSet'   { Write-Output ($ds) }
  86.         'DataTable' { Write-Output ($ds.Tables) }
  87.         'DataRow'   { Write-Output ($ds.Tables[0]) }
  88.     }
  89.  
  90. } #Invoke-Sqlcmd2

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me