Error in database.

[Jose Antonio_5]

Hi,

I think exists a database error because the notice_view has: .....and noticetype.noticetype=stringresource.subclass??? a string=integer???

What do you think??

Thanks & best regards,
Jose.

[Carlos Pascual_1]

I have find similar error in la view noticesview

The error message is:
Detalles: 22018:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'Insight.DiscoveredDevice ' to a column of data type int.

Any ideas?

[Jose Antonio_5]

Hi,

By the moment, I have to enter to resolve the problem ()

...
and cast(notices.NoticeType as varchar)=stringResource.ResourceName
....

[Carlos Pascual_1]

Try this:

ALTER VIEW notices_view AS (

select devices.Name as 'Device Name',

'Severity' = case when noticeSeverity=100 then 'Informational' when noticeSeverity = 1 then 'Normal' when noticeSeverity=2 then 'Warning' when noticeSeverity=3 then 'Minor' when noticeSeverity=4 then 'Major' when noticeSeverity=5 then 'Critical' else convert(char(10), noticeSeverity) end,

'state' = case when state=1 then 'Discovered' when state=2 then 'Not Cleared' when state = 5 then 'Cleared' when state = 6 then 'IN Progress' end,

generated, cleared, largeValue as Description from notices,

noticeType, devices, stringresource, stringtablelarge

where notices.noticeType = noticeType.noticeType and

notices.deviceKey = devices.deviceKey and

stringresource.resourceclassname='Alerttype' and

stringresource.subclass='description' and

noticeType.TypeIdStr=stringresource.resourceName and

stringtablelarge.id = stringresource.id

and stringTableLarge.language = 'en'


)

[Jose Antonio_5]

Hi Carlos,

I have made a little probe: (I have uncleared 267 events: 25 Critical, 43 Major, 46 Minor and 153 Warnings).

If i try to obtain the "description" field with your query, I obtain 208 events, but if I try to obtain the "description" field with this query, I obtain 267). Do you know why?

select notices.NoticeId, notices.NoticeSeverity, notices.Generated, devices.FullDNSName, devices.Name, devices.ProductName, devices.ProductSubType, devices.ProductTypeStr, notices.AssignedTo, notices.Comments, noticeType.TypeIdStr, noticeType.Category, noticeType.DispHandler, stringTableLarge.largeValue
from notices, noticeType, devices, stringResource, stringTableLarge
where ((notices.DeviceKey = devices.Devicekey)
and (notices.NoticeType = noticeType.NoticeType)
and (notices.NoticeSeverity<>100)
and (notices.State=2)
and (stringTableLarge.Language = 'en')
and stringResource.SubClass='description'
and cast(notices.NoticeType as varchar)=stringResource.ResourceName
and (stringTableLarge.id = stringResource.id))

By other hand, do you know the difference between "description" and "label"?

Thanks for your interest.