• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

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
0
SimonJohnG
Asked:
SimonJohnG
1 Solution
 
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
 
Louis01Commented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now