PoshCode Logo PowerShell Code Repository

Invoke-SqlCmd2 by Chad Miller 17 months ago (modification of post by Chad Miller view diff)
View followups from Chad Miller | diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2111"></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. Version History
  26. v1.0   - Chad Miller - Initial release
  27. v1.1   - Chad Miller - Fixed Issue with connection closing
  28. v1.2   - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
  29. #>
  30. function Invoke-Sqlcmd2
  31. {
  32.     [CmdletBinding()]
  33.     param(
  34.     [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
  35.     [Parameter(Position=1, Mandatory=$false)] [string]$Database,
  36.     [Parameter(Position=2, Mandatory=$false)] [string]$Query,
  37.     [Parameter(Position=3, Mandatory=$false)] [string]$Username,
  38.     [Parameter(Position=4, Mandatory=$false)] [string]$Password,
  39.     [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
  40.     [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
  41.     [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile
  42.     )
  43.  
  44.     if ($InputFile)
  45.     {
  46.         $filePath = $(resolve-path $InputFile).path
  47.         $Query =  [System.IO.File]::ReadAllText("$filePath")
  48.     }
  49.  
  50.     $conn=new-object System.Data.SqlClient.SQLConnection
  51.      
  52.     if ($Username)
  53.     { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
  54.     else
  55.     { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
  56.  
  57.     $conn.ConnectionString=$ConnectionString
  58.    
  59.     #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
  60.     if ($PSBoundParameters.Verbose)
  61.     {
  62.         $conn.FireInfoMessageEventOnUserErrors=$true
  63.         $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
  64.         $conn.add_InfoMessage($handler)
  65.     }
  66.    
  67.     $conn.Open()
  68.     $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
  69.     $cmd.CommandTimeout=$QueryTimeout
  70.     $ds=New-Object system.Data.DataSet
  71.     $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
  72.     [void]$da.fill($ds)
  73.     $conn.Close()
  74.     $ds.Tables
  75.  
  76. }

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