Solved

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

Posted on 2013-06-19
3
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 60

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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