The following 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 connections variables at the top of the script along with the $OuputFile and your SQL query at $SqlQuery
# SQL Connection Variables - Change $SQLServer = "SQLServerName.mydomain.local" $SQLDBName = "Database_Name" $SQLUsername = "SQLReaderUser" $SQLPassword = "MySQLPassword" # Where to save the output CSV file - Change $OuputFile = "c:\scripts\SQL_Export.csv" # Your SQL Query - Change $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
Hi, my result.
“3”;”ALMACEN CENTRAL”
“4”;”PLAZA LIMA SUR”
“5”;”PLAZA SAN MIGUEL”
“9”;”MEGA PLAZA”
Can you skip the quotes? “”
(Get-Content $OuputFile) | Foreach-Object {$_ -replace ‘”‘, “”} | Set-Content $OuputFile # optionally -Encoding ‘UTF8’
Hi,
Any way to use this via windows auth instead of sql user auth?
Exception calling “Fill” with “1” argument(s): “Login failed for user ‘********’.”
At C:\PawerBI\CmdFile\Job.ps1:37 char:1
+ $SqlAdapter.Fill($DataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SqlException
May i know what to check if the user cannot run the same script?
how do I keep the date format in yyyy-MM-dd HH: mm: ss.fff
long string gets capped away with …..
for example instead of returning the whole string the dataset trims it like
the quick brown fox……