PoshCode Logo PowerShell Code Repository

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

Modeled after SQL Server 2008 Invoke-Sqlcmd, but fixes bug in QueryTimeout.

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