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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy