Solved

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

Posted on 2013-06-19
3
317 Views
Last Modified: 2013-07-18
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
Comment
Question by:SimonJohnG
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39260303
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
 
LVL 2

Expert Comment

by:vivekkumarSharma
ID: 39261843
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
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
ID: 39261916
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now