PoshCode Logo PowerShell Code Repository

Invoke-Sql.ps1 by Chad Miller 28 months ago
View followups from dolmer and yoyon | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2484"></script>download | new post

Wrapper script for executing a T-SQL call and optionally returning a delimited file. Informational/Error messages are written to the Application EventLog. Purpose of script is run T-SQL commands from external scheduler (UniCenter, AutoSys, etc.), log messages to EventLog and return success/failed status.

  1. <#
  2. .SYNOPSIS
  3. Runs a T-SQL Query and optional outputs results to a delimited file.
  4. .DESCRIPTION
  5. Invoke-Sql script will run a T-SQL query or stored procedure and optionally outputs a delimited file.
  6. .EXAMPLE
  7. PowerShell.exe -File "C:\Scripts\Invoke-Sql.ps1" -ServerInstance "Z003\sqlprod2" -Database orders -Query "EXEC usp_accounts '12445678'"
  8. This example connects to Z003\sqlprod2.Orders and executes a stored procedure which does not return a result set
  9. .EXAMPLE
  10. PowerShell.exe -File "C:\Scripts\Invoke-Sql.ps1" -ServerInstance "Z003\sqlprod2" -Database orders -Query "SELECT * FROM dbo.accounts" -FilePath "C:\Scripts\accounts.txt" -Delimiter ","
  11. This example connects to Z003\sqlprod2.Orders and selects the records from the accounts tables, the data is outputed to a CSV file
  12. .NOTES
  13. Version History
  14. v1.0   - Chad Miller - 12/14/2010 - Initial release
  15. IMPORTANT!!! The EventLog source which is set to the application needs to be registered with
  16. the Event log:
  17. New-EventLog -LogName Application -Source  $Application
  18. #>
  19. param(
  20. #ServerInstance is Mandatory!
  21. [Parameter(Position=0, Mandatory=$false)] [string]$ServerInstance,
  22. #Database is Mandatory!
  23. [Parameter(Position=1, Mandatory=$false)] [string]$Database,
  24. #Query is Mandatory!
  25. [Parameter(Position=2, Mandatory=$false)] [string]$Query,
  26. [Parameter(Position=3, Mandatory=$false)] [string]$Application="Invoke-Sql.ps1",
  27. [Parameter(Position=4, Mandatory=$false)] [string]$FilePath,
  28. [Parameter(Position=7, Mandatory=$false)] [string]$Delimiter="|",
  29. #If UserName isn't supplied a trusted connection will be used
  30. [Parameter(Position=5, Mandatory=$false)] [string]$UserName,
  31. [Parameter(Position=6, Mandatory=$false)] [string]$Password,
  32. [Parameter(Position=8, Mandatory=$false)] [Int32]$QueryTimeout=600,
  33. [Parameter(Position=9, Mandatory=$false)] [Int32]$ConnectionTimeout=15
  34. )
  35.  
  36.  
  37. #This must be run as administrator on Windows 2008 and higher!
  38. New-EventLog -LogName Application -Source $Application -EA SilentlyContinue
  39. $Error.Clear()
  40.  
  41. #######################
  42. function Invoke-SqlCmd2
  43. {
  44.     param(
  45.     [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
  46.     [Parameter(Position=1, Mandatory=$true)] [string]$Database,
  47.     [Parameter(Position=2, Mandatory=$true)] [string]$Query,
  48.     [Parameter(Position=3, Mandatory=$false)] [string]$UserName,
  49.     [Parameter(Position=4, Mandatory=$false)] [string]$Password,
  50.     [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout,
  51.     [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout
  52.     )
  53.  
  54.     try {
  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.         $conn=new-object System.Data.SqlClient.SQLConnection
  60.         $conn.ConnectionString=$ConnectionString
  61.         $conn.Open()
  62.         $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
  63.         $cmd.CommandTimeout=$QueryTimeout
  64.         $ds=New-Object system.Data.DataSet
  65.         $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
  66.         [void]$da.fill($ds)
  67.         Write-Output ($ds.Tables[0])
  68.     }
  69.     finally {
  70.         $conn.Dispose()
  71.     }
  72.  
  73. } #Invoke-SqlCmd2
  74.  
  75. #######################
  76. #       MAIN          #
  77. #######################
  78. if ($PSBoundParameters.Count -eq 0)
  79. {
  80.  get-help $myInvocation.MyCommand.Path -full
  81.  break
  82. }
  83.  
  84. try {
  85.     $msg = $null
  86.     $msg += "Application/Job Name: $Application`n"
  87.     $msg += "Query: $Query`n"
  88.     $msg += "ServerInstance: $ServerInstance`n"
  89.     $msg += "Database: $Database`n"
  90.     $msg += "FilePath: $FilePath`n"
  91.    
  92.     Write-EventLog -LogName Application -Source "$Application" -EntryType Information -EventId 12345 -Message "Starting`n$msg"
  93.     $dt = Invoke-SqlCmd2 -ServerInstance $ServerInstance -Database $Database -Query $Query -UserName $UserName -Password $Password -QueryTimeOut $QueryTimeOut -ConnectionTimeout $ConnectionTimeout
  94.     if ($FilePath)
  95.     {
  96.         if ($dt)
  97.         { $dt | export-csv -Delimiter $Delimiter -Path $FilePath -NoTypeInformation }
  98.         else #Query Returned No Output!
  99.         {Write-EventLog -LogName Application -Source "$Application" -EntryType Warning -EventId 12345 -Message "NoOutput`n$msg" }
  100.     }
  101.  
  102.     Write-EventLog -LogName Application -Source "$Application" -EntryType Information -EventId 12345 -Message "Completed`n$msg"
  103. }
  104. catch {
  105.     $Exception = "{0}, {1}" -f  $_.Exception.GetType().FullName,$( $_.Exception.Message -replace "'" )
  106.     Write-EventLog -LogName Application -Source "$Application" -EntryType Error -EventId 12345 -Message "Error`n$msg`n$Exception"
  107.     throw
  108. }

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