Script failing with 'Conversion failed when converting the nvarchar value 'xxxxxxxxxx' to data type int.'

Had this working until I deleted by mistake, re-created it and now it throws up 'Conversion failed when converting the nvarchar value 'xxxxxxxxxx' to data type int.'

SELECT
  ESEventlogComputer.eventcomputer
  ,ESEventlogFilename.filename
  ,ESEventlogFilepath.filepath
  ,ESFilemonHistory.recorddate
  ,ESFilemonHistory.[action]
FROM
  ESFilemonHistory
  INNER JOIN ESEventlogComputer
    ON ESFilemonHistory.computername = ESEventlogComputer.id
  INNER JOIN ESEventlogFilepath
    ON ESFilemonHistory.filepath = ESEventlogFilepath.id
  INNER JOIN ESEventlogFilename
    ON ESFilemonHistory.filename = ESEventlogFilename.id
WHERE ESEventlogComputer.id IN (@User)
AND ESFilemonHistory.recorddate >= @StartDate AND ESFilemonHistory.recorddate < dateadd(day,1,@EndDate)

What silly thing have I missed?

Regards
S
SimonJohnGAsked:
Who is Participating?
 
Louis01Connect With a Mentor Commented:
I suspect you are joining on the wrong fields.
Maybe it should be something like:
SELECT
  ESEventlogComputer.eventcomputer
  ,ESEventlogFilename.filename
  ,ESEventlogFilepath.filepath
  ,ESFilemonHistory.recorddate
  ,ESFilemonHistory.[action]
FROM
  ESFilemonHistory
  INNER JOIN ESEventlogComputer
    ON ESFilemonHistory.computername = ESEventlogComputer.eventcomputer
  INNER JOIN ESEventlogFilepath
    ON ESFilemonHistory.filepath = ESEventlogFilepath.filepath
  INNER JOIN ESEventlogFilename
    ON ESFilemonHistory.filename = ESEventlogFilename.filename

Open in new window


Then, for your WHERE clause you have "WHERE ESEventlogComputer.id IN (@User)"

This won't work. I suspect you have a user id somewhere in your ESEventlogComputer table? Maybe called user_id? Then use "WHERE ESEventlogComputer.[user_id] = @User". (This assumes @User is a single value of the same type as ESEventlogComputer.[user_id])

Q:
1. What type is the @User variable?
2. What does it typically contain?
3. What field in ESEventlogComputer should @User be joined to?
0
 
Kevin CrossChief Technology OfficerCommented:
It is hard to tell without the table structure, but I suspect it is:

ESFilemonHistory.computername = ESEventlogComputer.id
or
ESFilemonHistory.filepath = ESEventlogFilepath.id
or
ESFilemonHistory.filename = ESEventlogFilename.id

If any of those (computername, filepath, or filename) are the real values versus the numeric id they are being joined to.
0
 
vivekkumarSharmaCommented:
In addition to mwvisa1

It may be possible that you are passing some string value in @user
in below statement.

ESEventlogComputer.id IN (@User)


There are no case exists for that in this query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.