cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Highlighted

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.
2 Replies
Highlighted
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 2 of 3

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?

Highlighted

Re: SQL query returning 'Error converting data type varchar to bigint.' error.

My apologies for the delayed response.

ePO version: 5.9.1 build 251

DLP Extension version: 11.1.100.16

DLP appliance extension version (just in case): 11.1.0.122

 

I only became aware of this error a week or so ago when we attempted to run the query I posted.

I have not attempted to run similar queries on other non-DLP related (this is a production server and we do not want to create any issues in the event other queries may crash something).

 

Thank you,

 Robert Clark

You Deserve an Award
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.

  • Find Forum FAQs
  • Learn How to Earn Badges
  • Ask for Help
Go to Community Help

Join the Community

    Thousands of customers use the McAfee Community for peer-to-peer and expert product support. Enjoy these benefits with a free membership:

  • Get helpful solutions from McAfee experts.
  • Stay connected to product conversations that matter to you.
  • Participate in product groups led by McAfee employees.
Join the Community
Join the Community