SQL query returning 'Error converting data type varchar to bigint.' error.
We are using the following query:
SELECT distinct t1.incidentid, t8.Username_NTLM, t8.UserName, t7.name, t1.totalcontentsize, t1.rulestodisplay, t1.connectivitystate, t1.insertiontime, t1.destination, t3.devicename, t3.deviceclassname, t3.devicedescription, t3.usbvendorid, t3.usbproductid, t3.usbserialnumber, t3.pluginutctime, t3.unpluggedutctime, plugdurationinsec, t2.filepath, t4.sourcepath, t5.printername, t6.CloudService, t8.UserGroups, t8.UserOU, t9.ApplicationFileName, t9.ApplicationProductName, t1.violationutctime WHERE * IS NOT NULL
FROM UDLP_Incidents as t1 with (nolock)
left outer join UDLP_IncidentEvidences as t2 with (nolock)
on (t1.incidentid = t2.IncidentId)
left outer join UDLP_IncidentDevice as t3 with (nolock)
on (t1.incidentid = t3.incidentid)
left outer join UDLP_IncidentRemovableStorage as t4 with (nolock)
on (t1.incidentid = t4.incidentid)
left outer join UDLP_IncidentPrint as t5 with (nolock)
on (t1.incidentid = t5.incidentid)
left outer join UDLP_IncidentCloud as t6 with (nolock)
on (t1.incidentid = t6.incidentid)
left outer join UDLP_EventComputers as t7 with (nolock)
on (t1.computerid = t7.id)
left outer join UDLP_EventUsers as t8 with (nolock)
on (t1.userid = t8.userid)
left outer join UDLP_IncidentApplications as t9 with (nolock)
on (t1.SourceApplicationId = t9.ApplicationId)
where t1.incidentID > '& LastGoodRecord & '
order by t1.incidentid
The purpose of this query is to provide targeted event from DLP.
When it is run, I receive this error (complete error response):
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
I suspect it is because of the UDLP_EventComputers table (specifically the Name field) that contains some empty values.
I am able to determine this with the following query:
SELECT * FROM UDLP_EventComputers WHERE Name = ''
This returns 641 rows
Can someone offer guidance on how to exclude those rows with empty names? All attempts so far result in a syntax error near nolock.
Re: SQL query returning 'Error converting data type varchar to bigint.' error.
Error converting data type varchar to bigint - I doubt this error has to do with whether the name field is blank or not. What version of epo and what dlp extension version? When did this issue start and what changed? If you recently upgraded epo, some of the table data types were changed to bigint to prevent the max value of around 2 billion autoid count that prevented any further events from being inserted. Do you get that same error with other queries not dlp related? If not, let me know and I will move this post over to dlp team to see if they have run across this.
Was my reply helpful? If this information was helpful in any way or answered your question, will you please select Accept as Solution in my reply and together we can help other members?
Don't forget, when your helpful posts earn a kudos or get accepted as a solution you can unlock perks and badges. Those aren't the only badges, either. How many can you collect? Click here to learn more.
Community Help Hub
New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.