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.

Code

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




http://technet.microsoft.com/en-us/library/hh849932.aspx
http://billfellows.blogspot.com/2011/03/powershell-export-query-to-csv.html
http://jasonq.com/2012/03/3-things-to-do-if-invoke-sqlcmd-is-not-recognized-in-windows-powershell/



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