PoshCode Logo PowerShell Code Repository

Invoke-SqlCommand.ps1 by Lee Holmes 32 months ago
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2188"></script>download | new post

From Windows PowerShell Cookbook (O’Reilly) by Lee Holmes

  1. ##############################################################################
  2. ##
  3. ## Invoke-SqlCommand
  4. ##
  5. ## From Windows PowerShell Cookbook (O'Reilly)
  6. ## by Lee Holmes (http://www.leeholmes.com/guide)
  7. ##
  8. ##
  9. ##############################################################################
  10.  
  11. <#
  12.  
  13. .SYNOPSIS
  14.  
  15. Return the results of a SQL query or operation
  16.  
  17. .EXAMPLE
  18.  
  19. Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders"
  20. Invokes a command using Windows authentication
  21.  
  22. .EXAMPLE
  23.  
  24. PS >$cred = Get-Credential
  25. PS >Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" -Cred $cred
  26. Invokes a command using SQL Authentication
  27.  
  28. .EXAMPLE
  29.  
  30. PS >$server = "MYSERVER"
  31. PS >$database = "Master"
  32. PS >$sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248"
  33. PS >Invoke-SqlCommand $server $database $sql
  34. Invokes a command that performs an update
  35.  
  36. .EXAMPLE
  37.  
  38. PS >$sql = "EXEC SalesByCategory 'Beverages'"
  39. PS >Invoke-SqlCommand -Sql $sql
  40. Invokes a stored procedure
  41.  
  42. .EXAMPLE
  43.  
  44. Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql "SELECT * FROM Users"
  45. Access an Access database
  46.  
  47. .EXAMPLE
  48.  
  49. Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql 'SELECT * FROM [Sheet1$]'
  50. Access an Excel file
  51.  
  52. #>
  53.  
  54. param(
  55.     ## The data source to use in the connection
  56.     [string] $DataSource = ".\SQLEXPRESS",
  57.  
  58.     ## The database within the data source
  59.     [string] $Database = "Northwind",
  60.  
  61.     ## The SQL statement(s) to invoke against the database
  62.     [Parameter(Mandatory = $true)]
  63.     [string[]] $SqlCommand,
  64.  
  65.     ## The timeout, in seconds, to wait for the query to complete
  66.     [int] $Timeout = 60,
  67.  
  68.     ## The credential to use in the connection, if any.
  69.     $Credential
  70. )
  71.  
  72.  
  73. Set-StrictMode -Version Latest
  74.  
  75. ## Prepare the authentication information. By default, we pick
  76. ## Windows authentication
  77. $authentication = "Integrated Security=SSPI;"
  78.  
  79. ## If the user supplies a credential, then they want SQL
  80. ## authentication
  81. if($credential)
  82. {
  83.     $credential = Get-Credential $credential
  84.     $plainCred = $credential.GetNetworkCredential()
  85.     $authentication =
  86.         ("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password)
  87. }
  88.  
  89. ## Prepare the connection string out of the information they
  90. ## provide
  91. $connectionString = "Provider=sqloledb; " +
  92.                     "Data Source=$dataSource; " +
  93.                     "Initial Catalog=$database; " +
  94.                     "$authentication; "
  95.  
  96. ## If they specify an Access database or Excel file as the connection
  97. ## source, modify the connection string to connect to that data source
  98. if($dataSource -match '\.xls$|\.mdb$')
  99. {
  100.     $connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " +
  101.         "Data Source=$dataSource; "
  102.  
  103.     if($dataSource -match '\.xls$')
  104.     {
  105.         $connectionString += 'Extended Properties="Excel 8.0;"; '
  106.  
  107.         ## Generate an error if they didn't specify the sheet name properly
  108.         if($sqlCommand -notmatch '\[.+\$\]')
  109.         {
  110.             $error = 'Sheet names should be surrounded by square brackets, ' +
  111.                 'and have a dollar sign at the end: [Sheet1$]'
  112.             Write-Error $error
  113.             return
  114.         }
  115.     }
  116. }
  117.  
  118. ## Connect to the data source and open it
  119. $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
  120. $connection.Open()
  121.  
  122. foreach($commandString in $sqlCommand)
  123. {
  124.     $command = New-Object Data.OleDb.OleDbCommand $commandString,$connection
  125.     $command.CommandTimeout = $timeout
  126.  
  127.     ## Fetch the results, and close the connection
  128.     $adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
  129.     $dataset = New-Object System.Data.DataSet
  130.     [void] $adapter.Fill($dataSet)
  131.  
  132.     ## Return all of the rows from their query
  133.     $dataSet.Tables | Select-Object -Expand Rows
  134. }
  135.  
  136. $connection.Close()

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