$svr='SqlServer'
$db = 'Database'
$cmd = Get-Content J:\Folder1\Query1.sql
$constr = "server=$svr;database=$db;Integrated Security=sspi"
(New-Object System.Data.SqlClient.SqlDataAdapter($cmd, $constr)).Fill($set) | out-null
$set.Tables[0] |
convertTo-CSV -noType -delimiter '|' |
% { $_.Replace('"','') } |
Out-File -Encoding ASCII "J:\Folder1\Output\Results_$(get-date -format 'yyyy_MM_dd').txt"
Note that I've changed the date format to something you can sort for.$cmd = @"
select a, b, c
from tbl
where x = y
"
$svr='SqlServer'
$db = 'Database'
$cmd = Get-Content J:\Folder1\Query1.sql
$constr = "server=$svr;database=$db;Integrated Security=sspi"
$set = New-Object Data.DataTable
(New-Object System.Data.SqlClient.SqlDataAdapter($cmd, $constr)).Fill($set) | out-null
$set.Tables[0] |
convertTo-CSV -noType -delimiter '|' |
% { $_.Replace('"','') } |
Out-File -Encoding ASCII "J:\Folder1\Output\Results_$(get-date -format 'yyyy_MM_dd').txt"
What the error in Sql Agent shall tell us, I don't know.
$svr='SqlServer'
$db = 'Database'
$cmd = Get-Content J:\Folder1\Query1.sql
$constr = "server=$svr;database=$db;Integrated Security=sspi"
$set = New-Object Data.DataTable
(New-Object System.Data.SqlClient.SqlDataAdapter($cmd, $constr)).Fill($set) | out-null
$set |
convertTo-CSV -noType -delimiter '|' |
% { $_.Replace('"','') } |
Out-File -Encoding ASCII "J:\Folder1\Output\Results_$(get-date -format 'yyyy_MM_dd').txt"