PoshCode Logo PowerShell Code Repository

Invoke-SqlCmd2 (v1.5.3) by Justin Dearing 25 months ago (modification of post by RCookieMonster view diff)
diff | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/5695"></script>download | new post

Modeled after SQL Server 2008 Invoke-Sqlcmd, but fixes bug in QueryTimeout, and allows for paramaterized queries and more!

  1. #######################
  2. <#
  3. .SYNOPSIS
  4.     Runs a T-SQL script.
  5.  
  6. .DESCRIPTION
  7.     Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified.
  8.     Paramaterized queries are supported.
  9.  
  10.     Help details below borrowed from Invoke-Sqlcmd.  Not verified by a SQL expert!
  11.  
  12. .PARAMETER ServerInstance
  13.     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".
  14.  
  15. .PARAMETER Database
  16.     A character string specifying the name of a database. Invoke-Sqlcmd2 connects to this database in the instance that is specified in -ServerInstance.
  17.  
  18. .PARAMETER Query
  19.     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".
  20.  
  21. .PARAMETER InputFile
  22.     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.
  23.  
  24. .PARAMETER Username
  25.     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.
  26.     When possible, use Windows Authentication.
  27.  
  28. .PARAMETER Password
  29.     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.
  30.     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.
  31.  
  32. .PARAMETER QueryTimeout
  33.     Specifies the number of seconds before the queries time out.
  34.  
  35. .PARAMETER ConnectionTimeout
  36.     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.
  37.  
  38. .PARAMETER As
  39.     Specifies output type - DataSet, DataTable, array of DataRow, or Single Value
  40.  
  41. .PARAMETER DBNullToNull
  42.     If specified, array of DataRow results will be converted to PSObject array with no DBNull values.
  43.     Props to Dave Wyatt http://powershell.org/wp/forums/topic/dealing-with-dbnull/
  44.  
  45. .INPUTS
  46.     None
  47.         You cannot pipe objects to Invoke-Sqlcmd2
  48.  
  49. .OUTPUTS
  50.    System.Data.DataTable
  51.  
  52. .EXAMPLE
  53.     Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
  54.    
  55.     This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
  56.     StartTime
  57.     -----------
  58.     2010-08-12 21:21:03.593
  59.  
  60. .EXAMPLE
  61.     Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
  62.    
  63.     This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
  64.  
  65. .EXAMPLE
  66.     Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
  67.  
  68.     This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
  69.     VERBOSE: hello world
  70.  
  71. .NOTES
  72. Version History
  73. v1.0   - Chad Miller - Initial release
  74. v1.1   - Chad Miller - Fixed Issue with connection closing
  75. v1.2   - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
  76. v1.3   - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
  77. v1.4   - Justin Dearing <zippy1981 _at_ gmail.com> - Added the ability to pass parameters to the query.
  78. v1.4.1 - Paul Bryson <atamido _at_ gmail.com> - Added fix to check for null values in parameterized queries and replace with [DBNull]
  79. v1.5   - Joel Bennett - add SingleValue output option
  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)
  82. v1.5.3 - Justin Dearing <zippy1981 _at_ gmail.com> - -Query now accepts pipeline input
  83. #>
  84. function Invoke-Sqlcmd2
  85. {
  86.     [CmdletBinding(
  87.         DefaultParameterSetName='Query'
  88.     )]
  89.     param(
  90.         [Parameter( Position=0, Mandatory=$true)]
  91.         [string]$ServerInstance,
  92.    
  93.         [Parameter( Position=1, Mandatory=$false)]
  94.         [string]$Database,
  95.    
  96.         [Parameter( Position=2,
  97.                     Mandatory=$true,
  98.                     ParameterSetName="Query",
  99.                     ValueFromPipeline = $true)]
  100.         [string]$Query,
  101.    
  102.         [Parameter( Position=2,
  103.                     Mandatory=$true,
  104.                     ParameterSetName="File")]
  105.         [ValidateScript({test-path $_})]
  106.         [string]$InputFile,
  107.  
  108.         [Parameter(Position=3, Mandatory=$false)]
  109.         [string]$Username,
  110.    
  111.         [Parameter(Position=4, Mandatory=$false)]
  112.         [string]$Password,
  113.    
  114.         [Parameter(Position=5, Mandatory=$false)]
  115.         [Int32]$QueryTimeout=600,
  116.    
  117.         [Parameter(Position=6, Mandatory=$false)]
  118.         [Int32]$ConnectionTimeout=15,
  119.    
  120.         [Parameter(Position=7, Mandatory=$false)]
  121.         [ValidateSet("DataSet", "DataTable", "DataRow","SingleValue")]
  122.         [string]$As="DataRow",
  123.    
  124.         [Parameter(Position=8, Mandatory=$false)]
  125.         [System.Collections.IDictionary]$SqlParameters,
  126.  
  127.         [switch]$DBNullToNull
  128.     )
  129.  
  130.     if ($InputFile)
  131.     {
  132.         $filePath = $(Resolve-Path $InputFile).path
  133.         $Query =  [System.IO.File]::ReadAllText("$filePath")
  134.     }
  135.  
  136.     $conn = New-Object System.Data.SqlClient.SQLConnection
  137.      
  138.     if ($Username)
  139.     { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
  140.     else
  141.     { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
  142.  
  143.     $conn.ConnectionString = $ConnectionString
  144.      
  145.     #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
  146.     if ($PSBoundParameters.Verbose)
  147.     {
  148.         $conn.FireInfoMessageEventOnUserErrors=$true
  149.         $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
  150.         $conn.add_InfoMessage($handler)
  151.     }
  152.      
  153.     $conn.Open()
  154.  
  155.     $cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
  156.     $cmd.CommandTimeout=$QueryTimeout
  157.  
  158.     if ($SqlParameters -ne $null)
  159.     {
  160.         $SqlParameters.GetEnumerator() |
  161.             ForEach-Object {
  162.                 If ($_.Value -ne $null)
  163.                 { $cmd.Parameters.AddWithValue($_.Key, $_.Value) }
  164.                 Else
  165.                 { $cmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) }
  166.             } > $null
  167.     }
  168.    
  169.     $ds = New-Object system.Data.DataSet
  170.     $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
  171.    
  172.     [void]$da.fill($ds)
  173.     $conn.Close()
  174.  
  175.     #This code scrubs DBNulls
  176.     $cSharp = @'
  177.        using System;
  178.        using System.Data;
  179.        using System.Management.Automation;
  180.  
  181.        public class DBNullScrubber
  182.        {
  183.            public static PSObject DataRowToPSObject(DataRow row)
  184.            {
  185.                PSObject psObject = new PSObject();
  186.  
  187.                if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached)
  188.                {
  189.                    foreach (DataColumn column in row.Table.Columns)
  190.                    {
  191.                        Object value = null;
  192.                        if (!row.IsNull(column))
  193.                        {
  194.                            value = row[column];
  195.                        }
  196.  
  197.                        psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
  198.                    }
  199.                }
  200.  
  201.                return psObject;
  202.            }
  203.        }
  204. '@
  205.  
  206.     switch ($As)
  207.     {
  208.         'DataSet'
  209.         {
  210.             $ds
  211.         }
  212.         'DataTable'
  213.         {
  214.             $ds.Tables
  215.         }
  216.         'DataRow'
  217.         {
  218.             if(-not $DBNullToNull)
  219.             {
  220.                 $ds.Tables[0]
  221.             }
  222.             else
  223.             {
  224.                 #Scrub DBNulls if specified.
  225.                 #Provides convenient results you can use comparisons with
  226.                 #Introduces overhead (e.g. ~2000 rows w/ ~80 columns went from .15 Seconds to .65 Seconds - depending on your data could be much more!)
  227.                 Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data','System.Xml'
  228.  
  229.                 foreach ($row in $ds.Tables[0].Rows)
  230.                 {
  231.                     [DBNullScrubber]::DataRowToPSObject($row)
  232.                 }
  233.             }
  234.         }
  235.         'SingleValue'
  236.         {
  237.             $ds.Tables[0] | Select-Object -Expand $ds.Tables[0].Columns[0].ColumnName
  238.         }
  239.     }
  240.  
  241. } #Invoke-Sqlcmd2

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