Following is the QUERY from logparser :
logparser -i:Textline "SELECT TO_DATE(TO_TIMESTAMP(TRIM(EXTRACT_SUFFIX(text,0,'TIMESTAMP')),'M/d/yyyy'))AS DATE, TO_TIME(TO_TIMESTAMP(TRIM(EXTRACT_TOKEN(text,0,'(')),'H:mm:ss')) as TIME from 'C:\xxxx.log' where text like '%OUT:%' OR text like '%IN:%' OR text like '%TIMESTAMP%' OR text like '%DENIED%'" -o:datagrid
Output of the query is :
DATE TIME
NULL 03:09:49
NULL 03:09:49
NULL 03:10:44
NULL 03:11:52
2015-02-06 03:12:40
NULL 03:13:37
NULL 03:18:22
NULL 03:21:18
NULL 03:21:32
NULL 03:24:19
NULL 03:26:02
When i want this data to flow into SQL table I get the following error:
QUERY :
logparser -i:Textline "select TO_DATE(TO_TIMESTAMP(TRIM(EXTRACT_SUFFIX(text,0,'TIMESTAMP')),'M/d/yyyy'))AS DATE, TO_TIME(TO_TIMESTAMP(TRIM(EXTRACT_TOKEN(text,0,'(')),'H:mm:ss')) as TIME INTO Ptclog from 'C:\ptclmgrd.log' where text like '%OUT:%' OR text like '%IN:%' OR text like '%TIMESTAMP%' OR text like '%DENIED%'" -o:SQL -server:KANNA\SQLEXPRESS -database:Flex -driver:"SQL Server Native Client 11.0" -username:sa -password:XXXXXX
ERROR:
Task aborted. SQL table column "Time" data type is not compatible with SELECT clause item "TIME" (type TIMESTAMP)
The data type selected in SQLEXPRESS is
time(0).
The data flows to the column if i select the datatype as :
"datetime"
But this adds a default date in front of time which is nuisance. e.g the time is shown as :
DATE TIME
2015-02-06 2015-01-01 03:12:40.000
NULL 2015-01-01 07:55:49.000
NULL 2015-01-01 06:46:50.000
NULL 2015-01-01 06:55:40.000
NULL 2015-01-01 01:57:25.000
NULL 2015-01-01 02:36:41.000
Is there a way the time can be represented in hh:mm:ss format in sql express 2014 DB ?
Following are sample entries in the log file that is being queried:
3:12:40 (lmgrd) TIMESTAMP 2/6/2015
3:13:37 (ptc_d) OUT: "PROE_Flex3CG" e1cx@D43F2STC
3:18:22 (ptc_d) OUT: "PROE_Flex3C" u234566@D5712Sf8
3:21:18 (ptc_d) OUT: "PROE_Flex3C" u234566@D5712Sf8
3:21:32 (ptc_d) OUT: "PROE_Flex3C" U524863@D9NR972S
3:24:19 (ptc_d) IN: "PROE_Flex3CG" e1cx@D43F2STC
3:26:02 (ptc_d) OUT: "PROE_Flex3C" m0ax@2SD3TC3F
3:26:46 (ptc_d) OUT: "PROE_Flex3C" 06u1015@03tjn-proe
3:26:48 (ptc_d) OUT: "26" 06u1015@03tjn-proe
3:26:48 (ptc_d) OUT: "10114" 06u1015@03tjn-proe
4:34:39 (ptc_d) DENIED: "26" 74U1027@MZBOXSGH61108 (Licensed number of users already reached. (-4,342:10054 ""))
Unfortunately no, LogParser does not support a time-of-day type. You may try to send your DATE and TIME columns together as a single Datetime, or send the TIME column as a string and hope SQL server will convert it to a Time...