November 20, 2019 at 1:54 pm
Hi,
i would like to generate csv file with datetime through sql job with below power shell script.But it didn't work.
Any suggessions.
$dt=get-date -format "_yyyymmmd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM [xxxxxx].[xxxx].[xxxxx]" -ServerInstance "LocalHost" |
Export-Csv -Path D:\folder\data$dt.csv" -NoTypeInformation
Thanks.
November 20, 2019 at 2:27 pm
Sure, here are some suggestions:
$dt=get-date -format "_yyyymmmd_HHmss"
What is this supposed to return exactly? I have never met a 3 digit month before nor can I write any minute above 9 because there is no space for 2 digits? The minute part might just work, the 3 digit month definitely not.
$dt=get-date -format "_yyyyMMdd_HHmmss"
works excellently for me, yours did too but I got data_20192220_xxxxxx.csv instead as a result.
November 20, 2019 at 4:10 pm
I have never met a 3 digit month before
MMM notation for a month means the first 3 characters of the name of the month? m and d, etc, are perfectly valid for the minutes and days though, and would return the value without a leading zero. So today's day, with the format yyyyMMMd would return 2019Nov20. For a date and time like 2019-12-07 17:09:03 the format yyyyMMd_HHmss would return 2019Dec7_17903; which I won't lie, is a bizarre value and would hope I never have to work with dates in that format.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 20, 2019 at 4:45 pm
okay I think then we solved the puzzle, the issue is
$dt=get-date -format "_yyyymmmd_HHmss"
should be
$dt=get-date -format "_yyyyMMMdd_HHmss"
I personally never thought of MMM as "Nov" - which admittedly makes sense (and for me begs a different question whose answer is probably well documented - how would you get full month names in this context?) - but Thom A is spot on right, and I think I recall learning somewhere that case sensivity matters here because otherwise it would be hard to guess if you want a double digit minute ("mm") or a month ("MM") in that position of the string.
Just on a sidenote: if you intend to process the generated file somewhere else based on the date part, I would still suggest going with double digit minutes like
$dt=get-date -format "_yyyyMMMdd_HHmmss"
because this makes parsing a date from the filename easier.
November 20, 2019 at 5:04 pm
(and for me begs a different question whose answer is probably well documented - how would you get full month names in this context?) .
That would be MMMM:
PS C:\> get-date -format "yyyy MMMM dd"
2019 November 20
Custom date and time format strings might be of interest to you.
Personally, for a consistently sized value, I would use yyyyMMdd_HHmmss which would translate in words to: "4 digit year, 2 digit month with leading zero, 2 digit day with leading zero, underscore, 2 digit hour with leading zero using 24 hour values, 2 digit minutes with leading zero, 2 digit seconds with leading zero".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 20, 2019 at 6:51 pm
Hi,
i would like to generate csv file with datetime through sql job with below power shell script.But it didn't work.
Any suggessions.
$dt=get-date -format "_yyyymmmd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM [xxxxxx].[xxxx].[xxxxx]" -ServerInstance "LocalHost" |
Export-Csv -Path D:\folder\data$dt.csv" -NoTypeInformation
Thanks.
Not sure what didn't work - did you get an error, an incorrect file name - something else?
For the file name, I would do this:
$fileName = "D:\folder\data$(dt).csv";
Then
... | Export-Csv -Path $fileName -NoTypeInformation
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 21, 2019 at 1:21 am
Sorry I was busy with prod issue after this post .i will check your suggestions tomorrow and reply.
Thank you for your suggestions.
November 21, 2019 at 1:46 pm
Hi,
I tried with below script through sql job and its failing with below error.
script:
$dt=get-date -format "_yyyyMMMdd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM [XXXXX].[XXX].[XXX]" -ServerInstance "LocalHost" |
Export-Csv -Path D:\folder\data$dt.csv" -NoTypeInformation
Error Message:
Executed as user: XXXXX. Unhandled Exception:System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args). Process Exit Code 255. The step failed.
November 21, 2019 at 3:44 pm
Hi Jeffrey,
thank you for your response.
I tried with your suggestion but it gives different error below.
script:
$dt=get-date -format "_yyyymmmd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM XXXXXXX" -ServerInstance "LocalHost"
$fileName = "D:\folder\data$(dt).csv" |
Export-Csv -Path $fileName -NoTypeInformation
Error:
Unable to start execution of step 1 (reason: The job step contains tokens. For SQL Server 2005 Service Pack 1 or later, you must use the appropriate ESCAPE_xxx macro to update job steps containing tokens before the job can run.). The step failed.
December 2, 2019 at 7:57 pm
OK, I played with this a bit and got this to work:
$dt = get-date -format "_yyyyMMMdd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM [dbo].[Customer]" -ServerInstance "Plato\SQL2017" -Database "Sandbox" |
Export-Csv -Path "E:\Documents\sql\$($dt).csv" -NoTypeInformation
I explicitly chose the database, which is something I don't think matters. If I add the database as a three part name and remove the -Database parameter, it still works.
What I think it broken is your use of a double quotes at the end of the -Path parameter without one at the beginning. I added that, along with expression expansion, and this works. I think the period causes issues for PoSh with the end of the name. If I remove all the quotes, this also works.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply