by wbbigdave at 2012-10-05 08:23:51
Hello,by surveyor at 2012-10-05 11:56:26
I am trying to get powershell to drop the results of a Stored Procedure to a CSV file for exporting to another system which doesn’t play nice with SQL in the first place.
After learning Powershell enough to write a semi decent script I hit a problem that in the SP the datetime fields contain millisecond data which is needed in the external application, however Powershell does not pull this data from the Storedprocedure output.
This isn’t anything to do with the export-CSV cmdlet as if i simply file-out the data I can’t see a millisecond output.
Can anyone help as my PowerShellFu is not good enough.
Without any knowledge what you have tried yet it’s very hard to say what you need. Can you show the PowerShell-Script which makes the problem?by RichardSiddaway at 2012-10-06 02:05:16
.NET DateTime Millisecond
PowerShell can work with millisecondsby wbbigdave at 2012-10-06 02:29:09
You need to break the data down
PS> $date = Get-Date -Year 2012 -Month 10 -Day 10 -Hour 4 -Minute 31 -Second 16 -Millisecond 367
PS> $date
10 October 2012 04:31:16
This also works
PS> [datetime]‘10/10/2012 4:32:16.367’
10 October 2012 04:32:16
Ach the code is on my work laptop but basically I am creating a sql connection and pulling the data with the Sql adapter into a dataset. This is then export-null and a table is populated. If I look st this table in PowerShell or export-csv it, then no milliseconds.by wbbigdave at 2012-10-08 00:13:05
How would I break the data down in the table? I tried formatting it and i can’t remember exactly how, something to do with calling the start_time field against the date time I got under a specific format but when I export-csv’ d it I only got that column.
This is the code i have written minus the variables.by wbbigdave at 2012-10-12 02:53:26$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
#Data Manipulation
$DataSet = New-Object System.Data.DataSet
$DataOut = $SqlAdapter.Fill($DataSet)
$DataOut | Out-Null
#Export Data to Hash Table
$DataTable = $DataSet.Tables[0]
#Export Has table to CSV
$DataTable | Export-CSV -Delimiter "," -Encoding Unicode -Path &OutputPath -NoTypeInformation
$SqlConnection.Close()
Does anyone have any ideas. I am running out of time and patience with this problem now. I know i can get-date with milliseconds, to an extent, but I really need the data pulled using this script to come in with the millisecond data.by Klaas at 2012-10-12 03:08:35
I tried your code on an existing table with datetime. When I ‘SELECT datetimefield FROM Mytable’ there are no milliseconds. When I ‘SELECT CONVERT(VARCHAR(30),datetimefield,113) FROM Mytable’ the milliseconds are back.by wbbigdave at 2012-10-12 03:16:44
So maybe adapting your query fixes everything. Of course it depends on your SQL Version and the format you want, there’s plenty of choice.
so if i alter my stored procedure to convert the datetime to a varchar you think it might work when i pull from the DB? Ill give it a whizz and try it, it;s SQL 2000 btw. Not my choice stupid pre installed application version.by wbbigdave at 2012-10-12 03:23:48
hmm looking at my SP again I don’t think I can edit the way it SELECTs the data. Here is the SP, it calls a pre defined SP which I definately cannot editby Klaas at 2012-10-12 04:16:03set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SAP_proc]
as
DECLARE @return_value int
DECLARE @starttime datetime
DECLARE @endtime datetime
SET @starttime = DATEADD (dd, -30, CURRENT_TIMESTAMP)
SET @endtime = CURRENT_TIMESTAMP
EXEC @return_value = [dbo].[sp_call_]
@starttime
,@endtime
SELECT ‘Return Value’ = @return_value
In all my tests Powershell writes to the csv exactly what is in the dataset. If there are ms in the data, then there are ms in the csv or in the shell or in a HTML, whatever the choice.by wbbigdave at 2012-10-17 05:43:00
I’m convinced the solution is on the SQL side, and your PSscript is fine.
Can you run a test with a SELECT to a view or table column with ms in it instead of the stored procedure in your $query?
I have run that Stored Procedure before now and when I save to a file from the results grid I get the results I need. I think the problem is in the export-csv output of the DataTable, at the moment I am trying to do a select on the Columns in the filled table. I can’t get my syntax right thoughby Klaas at 2012-10-17 06:50:37
$DataTable | select Start_Time, End_Time, @{n=datecolumn;e={$_.Sdatecolumn.ToString("yyyy/mm/dd hh:mm:ss.fff tt")}} | Export-CSV -Delimiter "," -Encoding Unicode -Path $OutputPath -NoTypeInformation
Not sure what the datecolumn bit should be in the array.
So if you replace ‘Export-csv’ with ‘out-gridview’ you do have the milliseconds?by wbbigdave at 2012-10-17 08:22:51
No,by wbbigdave at 2012-10-18 02:19:45
I am currently returning all of my data tables after I have done the export. I am trying different things with fill.schema but im not sure if this is working, also trying different things with the sql adapter.
If you have any advice on this then I would be incredibly grateful.
Ok different tact here, trying different options for loading the DataSet, tried all types of fill load options etc, the one I am currently trying is setting the DataSet.REmotingFormat but I cna’t set it to Binary, it keeps saying I have to use the Enumeration Values as "Xml, Binary" this is the code im trying to useby wbbigdave at 2012-10-22 06:23:01$DataSet.RemotingFormat = set Binary;
Help?by wbbigdave at 2012-10-23 07:17:12
Solved with the answer here
http://stackoverflow.com/questions/12857716/extract-millisecond-data-from-sql-stored-procedure