SQL: PowerShell and Exporting Query Results (CSV)
Author: jason
Date: 2014-02-06 16:17:52
Category: Musing

Here is a very easy to use PowerShell script to export a query from SQL as a CSV into the local server's file system. The query can be a SELECT query or a EXEC SPROC.


invoke-sqlcmd -query "exec master.dbo.sp_databases" -database master -serverinstance localhost | export-csv -path c:\temp\test.csv -NoTypeInformation

Here are the PowerShell script pieces
1) invoke-sqlcmd
2) -query "SELECT 'one' AS one"
3) -database master
4) -serverinstance localhost
5) | export-csv
6) c:\temp\test.csv
7) -NoTypeInformation

How easy is that? Sheesh...

There are 2 ways we can utilize PowerShell's INVOKE-SQLCMD. The first is from a PowerShell console window, started within SSMS. In the Object Explorer right-click the Server (or a database) and select Start PowerShell

Type/paste the script in and Enter to execute

The second way is from a SQL Server Agent Job. In the Job Step change the Type to PowerShell. Enter the script in the Command window.

And here is the file


jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009