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/2112"></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. #>
  31. function Invoke-Sqlcmd2
  32. {
  33.     [CmdletBinding()]
  34.     param(
  35.     [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
  36.     [Parameter(Position=1, Mandatory=$false)] [string]$Database,
  37.     [Parameter(Position=2, Mandatory=$false)] [string]$Query,
  38.     [Parameter(Position=3, Mandatory=$false)] [string]$Username,
  39.     [Parameter(Position=4, Mandatory=$false)] [string]$Password,
  40.     [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
  41.     [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
  42.     [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile
  43.     )
  44.  
  45.     if ($InputFile)
  46.     {
  47.         $filePath = $(resolve-path $InputFile).path
  48.         $Query =  [System.IO.File]::ReadAllText("$filePath")
  49.     }
  50.  
  51.     $conn=new-object System.Data.SqlClient.SQLConnection
  52.      
  53.     if ($Username)
  54.     { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
  55.     else
  56.     { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
  57.  
  58.     $conn.ConnectionString=$ConnectionString
  59.    
  60.     #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
  61.     if ($PSBoundParameters.Verbose)
  62.     {
  63.         $conn.FireInfoMessageEventOnUserErrors=$true
  64.         $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
  65.         $conn.add_InfoMessage($handler)
  66.     }
  67.    
  68.     $conn.Open()
  69.     $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
  70.     $cmd.CommandTimeout=$QueryTimeout
  71.     $ds=New-Object system.Data.DataSet
  72.     $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
  73.     [void]$da.fill($ds)
  74.     $conn.Close()
  75.     $ds.Tables
  76.  
  77. }

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