PoshCode Logo PowerShell Code Repository

Invoke-SqlCmdExe by Chad Miller 6 months ago
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/3756"></script>download | new post

Invoke-SqlCmdExes.ps1 script is a wrapper around sqlcmd.exe to run a T-SQL query or stored procedure and optionally outputs to a file.

  1. <#
  2. .SYNOPSIS
  3. Runs a T-SQL Query and optional outputs results to a file.
  4. .DESCRIPTION
  5. Invoke-SqlCmdExes.ps1 script is a wrapper around sqlcmd.exe to run a T-SQL query or stored procedure and optionally outputs to a file.
  6. .EXAMPLE
  7. PowerShell.exe -File "C:\Scripts\Invoke-SqlCmdExe.ps1" -ServerInstance "Z001\sql1" -Database accounting -Query "EXEC usp_accounts '12445678'"
  8. This example connects to Z001\sql1.accounting and executes a stored procedure which does not return a result set
  9. .EXAMPLE
  10. PowerShell.exe -File "C:\Scripts\Invoke-SqlCmdExe.ps1" -ServerInstance "Z001\sql1" -Database accounting -Query "SET NOCOUNT ON; SELECT * FROM dbo.vw_accounts" -FilePath "C:\Scripts\accounts.txt" -SqlCmdOptions '-h-1 -s"|" -w 8000'
  11. This example connects to Z001\sql1.accounting and selects the records from the vw_accounts view, the data is outputed to a pipe delimited file with additional options
  12. .EXAMPLE
  13. PowerShell.exe -File "C:\Scripts\Invoke-SqlCmdExe.ps1" -ServerInstance "Z001\sql1" -Database accounting -Query "EXEC usp_accounts '12445678'" -FilePath "C:\Scripts\accounts.txt" -SqlCmdOptions '-h-1 -s","'
  14. This example connects to Z001\sql1.accounting and selects the records from the vw_accounts view, the data is outputed to a CSV file
  15. .NOTES
  16. Version History
  17. v1.0   - Chad Miller - 5/3/2012 - Initial release
  18. #>
  19. param(
  20. [Parameter(Position=0, Mandatory=$true)]
  21. [string]
  22. $ServerInstance,
  23. [Parameter(Position=1, Mandatory=$true)]
  24. [string]
  25. $Database,
  26. [Parameter(Position=2, Mandatory=$true)]
  27. [string]
  28. $Query,
  29. [Parameter(Position=3, Mandatory=$false)]
  30. [ValidateNotNullOrEmpty()]
  31. [string]
  32. $Application="Invoke-SqlCmdExe.ps1",
  33. [Parameter(Position=4, Mandatory=$false)]
  34. [ValidateNotNullOrEmpty()]
  35. [ValidateScript({Test-Path $([system.io.path]::GetDirectoryName("$_"))})]
  36. [string]
  37. $FilePath,
  38. [Parameter(Position=5, Mandatory=$false)]
  39. [ValidateNotNullOrEmpty()]
  40. [string]
  41. #Additional Command-line Options for sqlcmd
  42. $SqlCmdOptions
  43. )
  44.  
  45.  
  46. #This must be run as administrator on Windows 2008 and higher!
  47. New-EventLog -LogName Application -Source $Application -EA SilentlyContinue
  48. $Error.Clear()
  49.  
  50. $events = @{"ApplicationStartEvent" = "31101"; "ApplicationStopEvent" = "31104"; "DatabaseException" = "31725"; "ConfigurationException" = "31705";"BadDataException" = "31760"}
  51. $msg =$null
  52. $MaxErrorMsgLen = 3000
  53.  
  54. #######################
  55. function Write-Message{
  56. Param([string]$Severity,[string]$Category,[string]$Eventid,[string]$ShortMessage=$null,[string]$Context=$null)
  57.    
  58.    $msg = @"
  59. Severity: $Severity
  60. Category: $Category
  61. EventID: $Eventid
  62. Short Message: $ShortMessage
  63. Context: $Context
  64. "@
  65.    $msg = $msg -replace "'"
  66.      
  67.   Write-EventLog -LogName Application -Source $Application -EntryType $Severity -EventId $Eventid -Category $Category -Message $msg              
  68.  
  69. } #Write-Message
  70.  
  71. #######################
  72. #       MAIN          #
  73. #######################
  74.  
  75. $Options = @"
  76. -S"$ServerInstance" -d "$Database" -Q "$Query"
  77. "@
  78.  
  79. if ($FilePath) {
  80.     $Options += @"
  81. -o "$FilePath"
  82. "@
  83. }
  84.  
  85. if ($SqlCmdOptions) {
  86.     $Options += " $SqlCmdOptions"
  87. }
  88.    
  89. Write-Verbose "sqlcmd.exe $Options"
  90.  
  91. $Context = "ServerInstance\Database = $ServerInstance\$Database`nQuery = $Query"
  92. $msg = "ApplicationStartEvent"
  93.  
  94. Write-Message -Severity Information -Category $events.ApplicationStartEvent -Eventid 1 -ShortMessage $msg -Context $Context
  95.    
  96. try {
  97.     if ($FilePath) {
  98.        $exitCode = (Start-Process -FilePath "sqlcmd.exe" -ArgumentList @"
  99. $Options
  100. "@ -Wait -NoNewWindow -Passthru).ExitCode
  101.     }
  102.     else {
  103.         $tempFile = [io.path]::GetTempFileName()
  104.         $exitCode = (Start-Process -FilePath "sqlcmd.exe" -ArgumentList @"
  105. $Options
  106. "@ -Wait -NoNewWindow -RedirectStandardOutput $tempFile -Passthru).ExitCode
  107.     }
  108.  
  109.     if ($ExitCode -eq 0) {
  110.         $msg = "ApplicationStopEvent"
  111.         Write-Message -Severity Information -Category $events.ApplicationStopEvent -Eventid 99 -ShortMessage $msg -Context $Context
  112.     }
  113.     else {
  114.         throw
  115.     }
  116. }
  117. #Start-Process Exception
  118. catch [InvalidOperationException] {
  119.     $Exception = "{0}, {1}" -f  $_.Exception.GetType().FullName,$( $_.Exception.Message -replace "'" )
  120.     Write-Verbose "InvalidOperationException"
  121.     Write-Message -Severity Error -Category $events.ConfigurationException -Eventid 99 -ShortMessage "ConfigurationException: $Exception" -Context $Context
  122.     throw $_
  123. }
  124. #SqlcmdException
  125. catch {
  126.     if ($FilePath) {
  127.         $Exception = [System.IO.File]::ReadAllText("$FilePath")
  128.     }
  129.     elseif ($tempFile) {
  130.         $Exception = [System.IO.File]::ReadAllText("$tempfile")
  131.     }
  132.  
  133.     if ($Exception -and $Exception.Length -gt $MaxErrorMsgLen) {
  134.         $Exception = $Exception.SubString($Exception.Length - $MaxErrorMsgLen)
  135.     }
  136.     Write-Verbose "SqlcmdException"
  137.     Write-Message -Severity Error -Category $events.DatabaseException -Eventid 99 -ShortMessage "DatabaseException: $Exception" -Context $Context
  138.     throw $Exception
  139. }
  140. finally {
  141.     if ($tempFile) {
  142.         remove-item $tempFile
  143.     }
  144. }

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