Thread Info
  • State Not Answered
  • Locked Locked
  • Replies 4 replies
  • Subscribers 2 subscribers
  • Views 1189 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Unable to execute SQL query command

AzwanArif


Hi All,

Below is the current customer deployment, We encounter issues with missing data control information for certain date 11th until 16th December 2020.
We have logged a case with Support and provided with SQL query to extract information. However, we encounter weird query report whereby instead of 2019 the error was trying to convert nvarchar value 2014.

Anyone encounter same issue or can help solve the solution?. Thanks

Sophos Environment:-


Sophos SEC (Windows 2012).
Sophos SQL Database (Windows 2012).

SQL Query & Error:-


SELECT * FROM [SOPHOS550].[Sophos Reporting Interface].[vEventsDataControlData] where EventTime >=2019-12-12



This thread was automatically locked due to age.
Parents
  • 0

    Hello AzwanArif,

    this is not an error with SEC or its database but with your query. You did not put the date value in quotes, thus it's treated as expression with an implied datatype of int(eger). SQL then tries to convert the EventTime (which is stored as nvchar) to int and fails. The value in the error message is the EventTime in the first row (SELECT TOP 1 EventTime  FROM [SOPHOS550].[Sophos Reporting Interface].[vEventsDataControlData] will output this very value).

    Christian

  • 0 in reply to QC

    Hi  

    Appreciate the feedback, I'm have execute below command with quotes at the date and running successfully without error.

    However, the output was started from 2014 to present date. I have browsed Sophos Interface Guide to export more detail,but with failure since I'm not familiar with the tools/query.

    Does the query able to list out only particular date and information output same as using report tools at console?. Thanks

    SQL Query:-

    SELECT EventTime "12/12/2019" FROM [SOPHOS550].[Sophos Reporting Interface].[vEventsDataControlData]

Reply
  • 0 in reply to QC

    Hi  

    Appreciate the feedback, I'm have execute below command with quotes at the date and running successfully without error.

    However, the output was started from 2014 to present date. I have browsed Sophos Interface Guide to export more detail,but with failure since I'm not familiar with the tools/query.

    Does the query able to list out only particular date and information output same as using report tools at console?. Thanks

    SQL Query:-

    SELECT EventTime "12/12/2019" FROM [SOPHOS550].[Sophos Reporting Interface].[vEventsDataControlData]

Children
  • 0 in reply to AzwanArif

    Hello AzwanArif,

    the Interface just provides consolidated data as SQL Views. It's assumed that you a) use the tool of your choice and b) know how to use it in order to get the desired results and reports. Thus if you use SQL a basic understanding of SQL concepts, data types, and syntax is a required before going into details.

    [Edit]
    On second thoughts, the above sounds too harsh, no insult intended, In your first query the missing quotes cause it to fail. SQL expects a condition after the WHERE clause, you use a comparison, the operands are expressions. An expression in turn can be almost anything, really. You are simply using a column (EvenData) and a constant - but unfortunately what you intended to be a constant constitutes (without the quotes) a syntactically valid expression..So SQL thinks it is one. Then it determines the datatypes of the operands and tries to find according to certain rules a common one, converting the operands as necessary (note: it does this per-row - thus this is not a syntax error).
    Your second query returns all the rows because SELECT column literal is short for SELECT column AS literal setting literal as alias for column (this is why you get it in the heading instead of EventData). And you have no selection criterion (that is specified with WHERE).

    Don't despair, it's not rocket science, at least the basics can be learned in a short time, I'd suggest the SQL Tutorial at w3schools.com.
    [/Edit]

    Christian

Unfiltered HTML