How do you run a SQL Server query from PowerShell?

by

For others who need to do this with just stock .NET and PowerShell (no additional SQL tools installed) here is the function that I use:

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
    
    $connection.Close()
    $dataSet.Tables

}

I have been using this so long I don’t know who wrote which parts. This was distilled from others’ examples, but simplified to be clear and just what is needed without extra dependencies or features.

I use and share this often enough that I have turned this into a script module on GitHub so that you can now go to your modules directory and execute git clone https://github.com/ChrisMagnuson/InvokeSQL and from that point forward invoke-sql will automatically be loaded when you go to use it (assuming your using PowerShell v3 or later).

More Related Contents:

  1. String handling from Powershell script
  2. Powershell script to delete 15 days old .txt files [duplicate]
  3. How to create a zip archive with PowerShell?
  4. How to run an EXE file in PowerShell with parameters with spaces and quotes
  5. Use PowerShell to run virus scan on multiple servers
  6. Fastest Way to get a uniquely index item from the property of an array
  7. Combine `Get-Disk` info and `LogicalDisk` info in PowerShell?
  8. Powershell remoting with ip-address as target
  9. Obtaining ExitCode using Start-Process and WaitForExit instead of -Wait
  10. Maximize window and bring it in front with powershell
  11. ConvertTo-Csv Output without quotes
  12. PowerShell – get process ID of called application
  13. Delete all files and folders but exclude a subfolder
  14. Start-Process with PowerShell.exe exhibits different behavior with embedded single quotes and double quotes
  15. Can the following Nested foreach loop be simplified in PowerShell?
  16. Add Write-Progress to Get-Job/Wait-Job
  17. Powershell with exchange– How do I append all verbose output to a file
  18. How to handle command-line arguments in PowerShell
  19. How to send email with PowerShell
  20. powershell function switch param with string array
  21. Why are my PowerShell exit codes always “0”?
  22. Escaping dollar signs in PowerShell path is not working
  23. Git Shell in Windows: patch’s default character encoding is UCS-2 Little Endian – how to change this to ANSI or UTF-8 without BOM?
  24. Adding an App Settings to existing Azure Web Application using Azure Power Shell
  25. .SelectSingleNode in Powershell script using xPath not working on extracting values from web.config file
  26. In powershell spawn notepad++ when file to open has spaces in it
  27. List of all colors available for PowerShell?
  28. Run Executable from Powershell script with parameters
  29. Expand variable inside single quotes
  30. Powershell script from shortcut to change desktop

Leave a Comment