Going back to my blog post about getting a SQL Server Query results out into Excel (Read more here and here), I’ve built another way which is quiet nice.

It outputs to a csv file and opens the folder location so you copy it out quickly. I was working on converting it to Excel, dropped the top row and converted it into the auto style tables. but i was having issues with the convert and largely no one really needs that extra work done so i dropped it.

<#

SMO

#>
## Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 

#Set up vars
$ExportServer = 'dev-sql01'
$ExportDB = 'TestDatabases'
$ExportQuery = 
@'
SELECT TOP (10) *
FROM Authors
'@
$ExportUserName =  read-host -Prompt "UserName"
$ExportPassword =  read-host -Prompt "Password"
# Set up Variables for export
$Today = Get-Date -Format yyyy-MM-dd-HH-mm-ss 
$FileName = "Export-" + $Today + ".csv"


$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ExportServer  

#This sets the connection to mixed-mode authentication 
$srv.ConnectionContext.LoginSecure=$false; 


#This sets the login name 
$srv.ConnectionContext.set_Login("$ExportUserName"); 
#This sets the password 
$srv.ConnectionContext.set_Password("$ExportPassword")  
$database = $srv.Databases["$ExportDB"]
$database.ExecuteWithResults("$ExportQuery").Tables[0]  | Export-Csv "C:\Results\$FileName"

# Open Explorer in Results folder
Set-Location "c:\Results"
Invoke-Item .

Advertisements