Reporting Error

The query runs fine here and I went through it line by line comparing data types to what I have in my database and I don't see any type mismatches. If you run the same query in SQL Studio do you get data back?

--

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

http://www.loop1systems.com/

http://www.linkedin.com/in/mrxinu

We are setting up a SQL Studio test server now to see what we get back. One of our programmers in devops seems to think that the error occured when we changed the outageduration in minutes variable. Not coming from a SQL background, it's been a learning experience haha.

Msg 208, Level 16, State 1, Line 1

Invalid object name 'Events'.

Here is the error message that appeared in SQL Studio.

mikelowery wrote: Msg 208, Level 16, State 1, Line 1 Invalid object name 'Events'. Here is the error message that appeared in SQL Studio.

In this case I think you're probably not targeting the right database. After you open a new query window if you look up and to the left, what does it say in the dropdown? If it still says "master" change that to your database and re-run your query. Alternatively, you can put the following above your query to make sure your query window has the right context:

USE SolarWindsOrion
GO

** Of course change from SolarWindsOrion to the name of your database if you changed it from the default.

--

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

http://www.loop1systems.com/

http://www.linkedin.com/in/mrxinu

mikelowery wrote: We are setting up a SQL Studio test server now to see what we get back. One of our programmers in devops seems to think that the error occured when we changed the outageduration in minutes variable. Not coming from a SQL background, it's been a learning experience haha.

Anything's possible I guess (since I can't reproduce it here) but you're not using the OutageDurationInMinutes field in any comparisons so the failure to cast (or convert) its data type from Int32 to DateTime doesn't make any sense. Typically you'll see that when you're trying to compare fields (WHERE x > y) or evaluate them (WHERE x + y = z) and you need both sides of the equation to be the same type so it can complete the work.

--

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

http://www.loop1systems.com/

http://www.linkedin.com/in/mrxinu

Hello Steven,

Thanks for the reply.

I'm still having the same issue. But I have narrowed it down to this: Now this is SQL Statement I'm working with::

SELECT MAX(VendorIcon) AS VendorIcon,  MAX(Caption) AS Caption,  MAX(StatusLED) AS StatusLED, NodeID, SUM(OutageDurationInMinutes) AS SumOutageDurationInMinutes  FROM (

  SELECT

  Nodes.VendorIcon,

  Nodes.Caption,

  Nodes.StatusLED,

  Nodes.NodeID,

  StartTime.Message,

  StartTime.EventTime AS DownEventTime,

  (

  SELECT TOP 1 EventTime

  FROM Events AS EndTimeTable

  where EndTimeTable.EventTime >= StartTime.EventTime

  AND EndTimeTable.EventType = 5

  AND EndTimeTable.NetObjectType = 'N'

  AND EndTimeTable.NetworkNode = StartTime.NetworkNode

  AND EventTime IS NOT NULL

  ORDER BY EndTimeTable.EventTime

  ) AS UpEventTime,

  DATEDIFF(Mi, StartTime.EventTime,(

  SELECT TOP 1 EventTime FROM Events AS Endtime

  where EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'

  AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime)

  ) AS OutageDurationInMinutes

  FROM Events StartTime

  INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

  WHERE (StartTime.EventType = 1)

) AS UpTimeTable

where outageDurationInMinutes IS NOT NULL

GROUP BY NodeID

ORDER BY Caption ASC

I think the Italicized syntax is what needs to change. Basically, I want to change the  SUM(OutageDurationInMinutes) to reflect this "HH24:MI:SS". I have tried numerous MYSQL Date Format but not working.

This is the screenshot below. The SUM of Outage Duration In Minutes is what needs to change to "HH24:MI:SS"..  Thanks for your help.

Hi oludads​ - you mention an outage duration in minutes and then a time format (HH24:MI:SS). Can you tell me what you'd envision that last column looking like? What would an outage duration in HH24:MI:SS look like?

--

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

http://www.loop1systems.com/

http://www.linkedin.com/in/mrxinu

Hello Steven,

Just wondering if you got my reply to the question you asked last week?

Thanks

I did and I asked a follow up question about what that'd look like. Did you see that? I think we're close to a solution but I need info.

I've followed up with a PM.