Solved

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

Posted on 2013-06-19
3
320 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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

23 Experts available now in Live!

Get 1:1 Help Now