tag:blogger.com,1999:blog-8792628637495446545.post395917743375743758..comments2024-03-25T09:54:29.303-04:00Comments on Lucas Notes: Querying SQL Error Log with Microsoft LogParserLucashttp://www.blogger.com/profile/14231394530002840714noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-8792628637495446545.post-46339821353075577562017-02-15T10:52:29.964-05:002017-02-15T10:52:29.964-05:00There is a space between Date and Time in the head...There is a space between Date and Time in the header file. They are being treated as 2 different columns. In the SQL query, I actually concatenate the Date + space + Time using the STRCAT function and then convert the concatenated string to date time format yyyy-MM-dd hh:mm:ss:<br /><br />SELECT TO_TIMESTAMP(STRCAT(STRCAT(DATE,' '), TIME), 'yyyy-MM-dd hh:mm:ss') as DateTime<br /><br />By the way, there is a gotcha when using the TO_TIMESTAMP. If it could not convert string, they would appear as NULL. Alternatively you can also not convert the string:<br /><br />SELECT STRCAT(STRCAT(DATE,' '), TIME) as DateTimeLucashttps://www.blogger.com/profile/14231394530002840714noreply@blogger.comtag:blogger.com,1999:blog-8792628637495446545.post-32887838931656069442017-02-13T17:29:36.625-05:002017-02-13T17:29:36.625-05:00So is there supposed to be a space between Date an...So is there supposed to be a space between Date and Time in the header file? I see you are not selecting two fields in the actual SQL query. I think that might be part of my confusion.<br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8792628637495446545.post-4650674733593243592017-02-11T22:07:45.362-05:002017-02-11T22:07:45.362-05:00Thank you for the feedback. I think the error that...Thank you for the feedback. I think the error that you are getting is because SQL Error Logs don't have column header. So we need to create a text file that map the column header. For SQL Error log, create a text file with any name you like (in my case, I name it "SQLErrorLogHeader.txt"). In the text file, I just have:<br /><br />Date Time Source Message<br /><br />Then when you run the Log Parser, use the following parameters:<br /><br />-iHeaderFile:C:\SQLErrorLogHeader.txt -headerRow:OFFLucashttps://www.blogger.com/profile/14231394530002840714noreply@blogger.comtag:blogger.com,1999:blog-8792628637495446545.post-59761737235936038312017-02-08T14:47:58.873-05:002017-02-08T14:47:58.873-05:00Having problems getting the TO_TIMESTAMP part to w...Having problems getting the TO_TIMESTAMP part to work for the DateTime. I keep on getting the error: <br /><br />Error: SELECT clause: Syntax Error: unknown field 'DATE'.<br />The closest match for input format 'TSV' is 'DateTime'.<br /><br />Thank you for posting this idea.<br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8792628637495446545.post-1350672100331424572013-03-18T22:12:54.787-04:002013-03-18T22:12:54.787-04:00For the SQLErrorLogHeader.txt header file, you can...For the SQLErrorLogHeader.txt header file, you can create a new text file and then put the following into a text file content:<br /><br />Date Time Source Message<br /><br />Let me know if it does not work.Lucashttps://www.blogger.com/profile/14231394530002840714noreply@blogger.comtag:blogger.com,1999:blog-8792628637495446545.post-73982779310061737112013-03-08T17:14:17.847-05:002013-03-08T17:14:17.847-05:00Great idea but I could not find the header file - ...Great idea but I could not find the header file - the link isn't working - could you post the file/new link?Anonymousnoreply@blogger.com