The below is an example PowerShell script to connect to a Microsoft SQL Database and dump the results of a query into a CSV file.
You will need to change the variables at the top along with the column names against the line starting “$DataSet.Tables[0] | select”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
#Connect to SQL and run QUERY $SQLServer = "SQLServerName.mydomain.local" $SQLDBName = "Database_Name" $SQLUsername = "SQLReaderUser" $SQLPassword = "MySQLPassword" $OuputFile = "c:\scripts\SQL_Export.csv" $SqlQuery = "SELECT rtrim(EmployeeNumber) as EmployeeNumber, rtrim(JobTitle) as JobTitle, rtrim(Department) as Department, rtrim(Company) as Company, rtrim(Location) as Location, rtrim(CostCentre) as CostCentre, rtrim(ManagerEmployeeNumber) as ManagerEmployeeNumber FROM [$SQLDBName].[dbo].[Employee_Basic]" ##Delete the output file if it already exists If (Test-Path $OuputFile ){ Remove-Item $OuputFile } Write-Host "INFO: Exporting data from $SQLDBName to $OuputFile" -foregroundcolor white -backgroundcolor blue ## - Connect to SQL Server using non-SMO class 'System.Data': $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $SQLUsername; Password = $SQLPassword" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $SqlQuery $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() #Output RESULTS to CSV $DataSet.Tables[0] | select "EmployeeNumber","JobTitle","Department","Company","Location","CostCentre","ManagerEmployeeNumber" | Export-Csv $OuputFile |
Author: Phil Eddies
I am an IT Operations Manager, managing all aspects of the IT infrastructure and service for a mid sized UK based company. I have been working full time in IT since 2001 in 1st to 3rd line and System Administration roles. MCSA, MCSE, CCNA, Citrix CCA
Recent Comments