PoshCode Logo PowerShell Code Repository

Difference between
modified post 5695 by Justin Dearing on Fri 16th Jan 19:50 and
original post 4967 by RCookieMonster on Sat 8th Mar 19:10
Showold version | new version | both versions

    
11
#######################
22
<#
33
.SYNOPSIS
44
    Runs a T-SQL script.
66
.DESCRIPTION
77
    Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified.
88
    Paramaterized queries are supported.
1010
    Help details below borrowed from Invoke-Sqlcmd.  Not verified by a SQL expert!
1212
.PARAMETER ServerInstance
1313
    A character string specifying the name of an instance of the Database Engine. For default instances, only specify the computer name: "MyComputer". For named instances, use the format "ComputerName\InstanceName".
1515
.PARAMETER Database
1616
    A character string specifying the name of a database. Invoke-Sqlcmd2 connects to this database in the instance that is specified in -ServerInstance.
1818
.PARAMETER Query
1919
    Specifies one or more queries to be run. The queries can be Transact-SQL (? or XQuery statements, or sqlcmd commands. Multiple queries separated by a semicolon can be specified. Do not specify the sqlcmd GO separator. Escape any double quotation marks included in the string ?). Consider using bracketed identifiers such as [MyTable] instead of quoted identifiers such as "MyTable".
2121
.PARAMETER InputFile
2222
    Specifies a file to be used as the query input to Invoke-Sqlcmd2. The file can contain Transact-SQL statements, (? XQuery statements, and sqlcmd commands and scripting variables ?). Specify the full path to the file.
2424
.PARAMETER Username
2525
    Specifies the login ID for making a SQL Server Authentication connection to an instance of the Database Engine. The password must be specified using -Password. If -Username and -Password are not specified, Invoke-Sqlcmd attempts a Windows Authentication connection using the Windows account running the PowerShell session.
2626
    When possible, use Windows Authentication.
2828
.PARAMETER Password
2929
    Specifies the password for the SQL Server Authentication login ID that was specified in -Username. Passwords are case-sensitive. When possible, use Windows Authentication. Do not use a blank password, when possible use a strong password. For more information, see "Strong Password" in SQL Server Books Online.
3030
    SECURITY NOTE: If you type -Password followed by your password, the password is visible to anyone who can see your monitor. If you code -Password followed by your password in a .ps1 script, anyone reading the script file will see your password. Assign the appropriate NTFS permissions to the file to prevent other users from being able to read the file.
3232
.PARAMETER QueryTimeout
3333
    Specifies the number of seconds before the queries time out.
3535
.PARAMETER ConnectionTimeout
3636
    Specifies the number of seconds when Invoke-Sqlcmd2 times out if it cannot successfully connect to an instance of the Database Engine. The timeout value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.
3838
.PARAMETER As
3939
    Specifies output type - DataSet, DataTable, array of DataRow, or Single Value
4141
.PARAMETER DBNullToNull
4242
    If specified, array of DataRow results will be converted to PSObject array with no DBNull values.
4343
    Props to Dave Wyatt http://powershell.org/wp/forums/topic/dealing-with-dbnull/
4545
.INPUTS
4646
    None
4747
        You cannot pipe objects to Invoke-Sqlcmd2
4949
.OUTPUTS
5050
   System.Data.DataTable
5252
.EXAMPLE
5353
    Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
5555
    This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
5656
    StartTime
5757
    -----------
5858
    2010-08-12 21:21:03.593
6060
.EXAMPLE
6161
    Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
6363
    This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
6565
.EXAMPLE
6666
    Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
6868
    This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
6969
    VERBOSE: hello world
7171
.NOTES
7272
Version History
7373
v1.0   - Chad Miller - Initial release
7474
v1.1   - Chad Miller - Fixed Issue with connection closing
7575
v1.2   - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
7676
v1.3   - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
7777
v1.4   - Justin Dearing <zippy1981 _at_ gmail.com> - Added the ability to pass parameters to the query.
7878
v1.4.1 - Paul Bryson <atamido _at_ gmail.com> - Added fix to check for null values in parameterized queries and replace with [DBNull]
7979
v1.5   - Joel Bennett - add SingleValue output option
80-
v1.5.1 - RamblingCookieMonster - Added ParameterSets, set Query and InputFile to mandatory
80+
v1.5.1 - RamblingCookieMonster - Added ParameterSets, set -Query and -InputFile to mandatory
81-
v1.5.2 - RamblingCookieMonster - Added DBNullToNull switch and code from Dave Wyatt.  Added parameters to comment based help (need someone with SQL expertise to verify these)
81+
v1.5.2 - RamblingCookieMonster - Added -DBNullToNull switch and code from Dave Wyatt.  Added parameters to comment based help (need someone with SQL expertise to verify these)
82+
v1.5.3 - Justin Dearing <zippy1981 _at_ gmail.com> - -Query now accepts pipeline input
8283
#>
8384
function Invoke-Sqlcmd2
8485
{
8586
    [CmdletBinding(
8687
        DefaultParameterSetName='Query'
8788
    )]
8889
    param(
8990
        [Parameter( Position=0, Mandatory=$true)]
9091
        [string]$ServerInstance,
9293
        [Parameter( Position=1, Mandatory=$false)]
9394
        [string]$Database,
9596
        [Parameter( Position=2,
9697
                    Mandatory=$true,
97-
                    ParameterSetName="Query")]
98+
                    ParameterSetName="Query",
99+
                    ValueFromPipeline = $true)]
98100
        [string]$Query,
100102
        [Parameter( Position=2,
101103
                    Mandatory=$true,
102104
                    ParameterSetName="File")]
103105
        [ValidateScript({test-path $_})]
104106
        [string]$InputFile,
106108
        [Parameter(Position=3, Mandatory=$false)]
107109
        [string]$Username,
109111
        [Parameter(Position=4, Mandatory=$false)]
110112
        [string]$Password,
112114
        [Parameter(Position=5, Mandatory=$false)]
113115
        [Int32]$QueryTimeout=600,
115117
        [Parameter(Position=6, Mandatory=$false)]
116118
        [Int32]$ConnectionTimeout=15,
118120
        [Parameter(Position=7, Mandatory=$false)]
119121
        [ValidateSet("DataSet", "DataTable", "DataRow","SingleValue")]
120122
        [string]$As="DataRow",
122124
        [Parameter(Position=8, Mandatory=$false)]
123125
        [System.Collections.IDictionary]$SqlParameters,
125127
        [switch]$DBNullToNull
126128
    )
128130
    if ($InputFile)
129131
    {
130132
        $filePath = $(Resolve-Path $InputFile).path
131133
        $Query =  [System.IO.File]::ReadAllText("$filePath")
132134
    }
134136
    $conn = New-Object System.Data.SqlClient.SQLConnection
136138
    if ($Username)
137139
    { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
138140
    else
139141
    { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
141143
    $conn.ConnectionString = $ConnectionString
143145
    #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
144146
    if ($PSBoundParameters.Verbose)
145147
    {
146148
        $conn.FireInfoMessageEventOnUserErrors=$true
147149
        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
148150
        $conn.add_InfoMessage($handler)
149151
    }
151153
    $conn.Open()
153155
    $cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
154156
    $cmd.CommandTimeout=$QueryTimeout
156158
    if ($SqlParameters -ne $null)
157159
    {
158160
        $SqlParameters.GetEnumerator() |
159161
            ForEach-Object {
160162
                If ($_.Value -ne $null)
161163
                { $cmd.Parameters.AddWithValue($_.Key, $_.Value) }
162164
                Else
163165
                { $cmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) }
164166
            } > $null
165167
    }
167169
    $ds = New-Object system.Data.DataSet
168170
    $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
170172
    [void]$da.fill($ds)
171173
    $conn.Close()
173175
    #This code scrubs DBNulls
174176
    $cSharp = @'
175177
        using System;
176178
        using System.Data;
177179
        using System.Management.Automation;
179181
        public class DBNullScrubber
180182
        {
181183
            public static PSObject DataRowToPSObject(DataRow row)
182184
            {
183185
                PSObject psObject = new PSObject();
185187
                if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached)
186188
                {
187189
                    foreach (DataColumn column in row.Table.Columns)
188190
                    {
189191
                        Object value = null;
190192
                        if (!row.IsNull(column))
191193
                        {
192194
                            value = row[column];
193195
                        }
195197
                        psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
196198
                    }
197199
                }
199201
                return psObject;
200202
            }
201203
        }
202204
'@
204206
    switch ($As)
205207
    {
206208
        'DataSet'
207209
        {
208210
            $ds
209211
        }
210212
        'DataTable'
211213
        {
212214
            $ds.Tables
213215
        }
214216
        'DataRow'
215217
        {
216218
            if(-not $DBNullToNull)
217219
            {
218220
                $ds.Tables[0]
219221
            }
220222
            else
221223
            {
222224
                #Scrub DBNulls if specified.
223225
                #Provides convenient results you can use comparisons with
224226
                #Introduces overhead (e.g. ~2000 rows w/ ~80 columns went from .15 Seconds to .65 Seconds - depending on your data could be much more!)
225227
                Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data','System.Xml'
227229
                foreach ($row in $ds.Tables[0].Rows)
228230
                {
229231
                    [DBNullScrubber]::DataRowToPSObject($row)
230232
                }
231233
            }
232234
        }
233235
        'SingleValue'
234236
        {
235237
            $ds.Tables[0] | Select-Object -Expand $ds.Tables[0].Columns[0].ColumnName
236238
        }
237239
    }
239241
} #Invoke-Sqlcmd2

ContributeMost Recent Contributions (feed)

Contribute ... Next Page