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 #######################
Invoke-SqlCmd2null42 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 }
109