473,320 Members | 1,821 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Syntax error converting the varchar value '10.136.20.20' to a column of data type int.

I have an inventory database that Im trying to create a report out of
the IP address are a lookup on a seperat table but I keep getting the
above error can I change the table row to something to fix this or
what.

SELECT i.INVENTORY_ITEM_ID AS [IP Address],i.HOST_NAME AS [Server
Name], '' AS Flag, i.MEMO AS Comments, 'Seattle' AS City, 'Washington'
AS State,
CASE WHEN fv.value = 'EL EET1410' THEN '1111 3rd
Ave.' WHEN fv.value = 'EL WFL17' THEN '999 3rd Ave.' ELSE '' END AS
[Address 1],
' ' AS [Address 2], CASE WHEN fv.value = 'EL
EET1410' THEN '1111' WHEN fv.value = 'EL WFL17' THEN '2222' ELSE ''
END AS [Building ID],
fv.VALUE AS Building, '98101' AS Zip,
CASE WHEN fv.value = 'EL EET1410' THEN '14' WHEN
fv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Computing Facility
Level],
CASE WHEN fv.value = 'EL EET1410' THEN '14' WHEN
fv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Bldg Floor],
CASE WHEN fv.value = 'EL EET1410' THEN 'C028'
WHEN fv.value = 'EL WFL17' THEN 'C123' ELSE '' END AS WSPID,
i.LOCATION_IN_FACILITY AS Location,
i.SERIAL_NUMBER AS [Serial Number], i.ASSET_TAG AS [Asset Tag], ' ' AS
[Second Asset Tag(s)],
vv.VALUE AS Manufacture, mv.VALUE AS Model,
'Yes' AS [Rack Mountable], rv.VALUE AS [Rack Units], 'DEV' AS [Server
Enviroment],
lv.VALUE AS [Sever Type], ov.VALUE AS OS,
CASE WHEN lv.value = 'Server - Intel Blade' THEN
'Windows' WHEN lv.value = 'Server - Intel' THEN 'Windows' WHEN
lv.value = 'Server - Unix' THEN 'Unix'
ELSE '' END AS [OS Type], '19x28' AS Footprint,
ev.VALUE AS [Technical Owner of Server], 'N/A' AS [SLA Category],
i.ON_BOARD_NIC_PORT_COUNT AS [NW Connection
quantity], 'Devlopment/Test Machine' AS [Application(s) Name],
ev.VALUE AS [PW Contact],
'N/A' AS RTO, 'N/A' AS RPO, 'No' AS [Is Box
Clustered?], 'N/A' AS [Buisness Critical],
CASE WHEN mv.value = 'Proliant DL580G1' THEN
'90lbs' WHEN mv.value = 'Proliant DL360G2' THEN '85lbs' WHEN mv.value
= 'Enterprise 220R' THEN
'45lbs' ELSE '' END AS Weight,
i.LAST_MODIFIED_DATE AS [Date of Last Install], 'No' AS [Dedicated
Circuit]
FROM dbo.INVENTORY_ITEM i LEFT OUTER JOIN
dbo.IP_TO_INVENTORY pv ON pv.IP_ADDRESS = i.INVENTORY_ITEM_ID
LEFT OUTER JOIN
dbo.LOOKUP_VALUE lv ON lv.LOOKUP_VALUE_ID =
i.DEVICE_TYPE_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE fv ON fv.LOOKUP_VALUE_ID =
i.FACILITY_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE vv ON vv.LOOKUP_VALUE_ID =
i.VENDOR_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE mv ON mv.LOOKUP_VALUE_ID =
i.MODEL_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE ov ON ov.LOOKUP_VALUE_ID =
i.SOFTWARE_VERSION_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE ev ON ev.LOOKUP_VALUE_ID =
i.CHECKED_OUT_BY_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE rv ON rv.LOOKUP_VALUE_ID =
i.U_HEIGHT_ID
Jul 20 '05 #1
2 7113
Hi

You don't post the ddl for the tables used but at a guess either
pv.IP_ADDRESS or i.INVENTORY_ITEM_ID is an it.

John

"Anthony" <an**************@wamu.net> wrote in message
news:fb**************************@posting.google.c om...
I have an inventory database that Im trying to create a report out of
the IP address are a lookup on a seperat table but I keep getting the
above error can I change the table row to something to fix this or
what.

SELECT i.INVENTORY_ITEM_ID AS [IP Address],i.HOST_NAME AS [Server
Name], '' AS Flag, i.MEMO AS Comments, 'Seattle' AS City, 'Washington'
AS State,
CASE WHEN fv.value = 'EL EET1410' THEN '1111 3rd
Ave.' WHEN fv.value = 'EL WFL17' THEN '999 3rd Ave.' ELSE '' END AS
[Address 1],
' ' AS [Address 2], CASE WHEN fv.value = 'EL
EET1410' THEN '1111' WHEN fv.value = 'EL WFL17' THEN '2222' ELSE ''
END AS [Building ID],
fv.VALUE AS Building, '98101' AS Zip,
CASE WHEN fv.value = 'EL EET1410' THEN '14' WHEN
fv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Computing Facility
Level],
CASE WHEN fv.value = 'EL EET1410' THEN '14' WHEN
fv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Bldg Floor],
CASE WHEN fv.value = 'EL EET1410' THEN 'C028'
WHEN fv.value = 'EL WFL17' THEN 'C123' ELSE '' END AS WSPID,
i.LOCATION_IN_FACILITY AS Location,
i.SERIAL_NUMBER AS [Serial Number], i.ASSET_TAG AS [Asset Tag], ' ' AS
[Second Asset Tag(s)],
vv.VALUE AS Manufacture, mv.VALUE AS Model,
'Yes' AS [Rack Mountable], rv.VALUE AS [Rack Units], 'DEV' AS [Server
Enviroment],
lv.VALUE AS [Sever Type], ov.VALUE AS OS,
CASE WHEN lv.value = 'Server - Intel Blade' THEN
'Windows' WHEN lv.value = 'Server - Intel' THEN 'Windows' WHEN
lv.value = 'Server - Unix' THEN 'Unix'
ELSE '' END AS [OS Type], '19x28' AS Footprint,
ev.VALUE AS [Technical Owner of Server], 'N/A' AS [SLA Category],
i.ON_BOARD_NIC_PORT_COUNT AS [NW Connection
quantity], 'Devlopment/Test Machine' AS [Application(s) Name],
ev.VALUE AS [PW Contact],
'N/A' AS RTO, 'N/A' AS RPO, 'No' AS [Is Box
Clustered?], 'N/A' AS [Buisness Critical],
CASE WHEN mv.value = 'Proliant DL580G1' THEN
'90lbs' WHEN mv.value = 'Proliant DL360G2' THEN '85lbs' WHEN mv.value
= 'Enterprise 220R' THEN
'45lbs' ELSE '' END AS Weight,
i.LAST_MODIFIED_DATE AS [Date of Last Install], 'No' AS [Dedicated
Circuit]
FROM dbo.INVENTORY_ITEM i LEFT OUTER JOIN
dbo.IP_TO_INVENTORY pv ON pv.IP_ADDRESS = i.INVENTORY_ITEM_ID
LEFT OUTER JOIN
dbo.LOOKUP_VALUE lv ON lv.LOOKUP_VALUE_ID =
i.DEVICE_TYPE_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE fv ON fv.LOOKUP_VALUE_ID =
i.FACILITY_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE vv ON vv.LOOKUP_VALUE_ID =
i.VENDOR_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE mv ON mv.LOOKUP_VALUE_ID =
i.MODEL_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE ov ON ov.LOOKUP_VALUE_ID =
i.SOFTWARE_VERSION_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE ev ON ev.LOOKUP_VALUE_ID =
i.CHECKED_OUT_BY_ID LEFT OUTER JOIN
dbo.LOOKUP_VALUE rv ON rv.LOOKUP_VALUE_ID =
i.U_HEIGHT_ID

Jul 20 '05 #2
sa***************@wamu.net (Anthony) wrote in message news:<fb**************************@posting.google. com>...
I have an inventory database that Im trying to create a report out of
the IP address are a lookup on a seperat table but I keep getting the
above error can I change the table row to something to fix this or
what.

I believe you will find the datatype of one of the following to be an
integer, not a string.
dbo.INVENTORY_ITEM.INVENTORY_ITEM_ID
or
dbo.IP_TO_IVNETORY.IP_ADDRESS

FROM dbo.INVENTORY_ITEM i

LEFT OUTER JOIN dbo.IP_TO_INVENTORY pv
ON pv.IP_ADDRESS = i.INVENTORY_ITEM_ID
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Toonman | last post by:
I'm trying to use a couple of variables in a stored procedure. Things work fine when I hard code the data into the variables and also work fine when I use the variable in the WHERE clause and hard...
2
by: Anthony | last post by:
I have an inventory database that Im trying to create a report out of the IP address are a lookup on a seperat table but I keep getting the above error can I change the table row to something to...
4
by: Viktor Popov | last post by:
Hi, I'm trying to execute stored procedure and I collect its parameters from WebForm. There is an error: Error converting data type varchar to int. What could be the problem? Here it is the...
1
by: luna | last post by:
got so far then it broke and i cant get it working again - it was updating fine but not inserting and now im getting a "Error converting data type varchar to numeric" which i didnt have before.......
7
by: Dan | last post by:
Hi. I'm having a problem with searching a databse entry. I'm new to dotnet and still trying to figure some things out. In order to locate a row in an access databse file .mdb, i am using this...
1
by: ronca | last post by:
Error Syntax error converting the varchar value $568.25 to a column of data type Statement I'm using Select Top 1000 * From mass.dbo.dbo_MASS where _amount > 5000 Want only row with _amount...
3
by: othellomy | last post by:
select convert(float,'1.2334e+006') 1233400.0 select convert(decimal(20,2),'1.2334e+006') Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric. can I set...
0
Krishna Ladwa
by: Krishna Ladwa | last post by:
In Sql Server 2000 Version, I found that no Notification message box appears when converting text column to varchar but the data gets truncated to the given size for the varchar. Whereas it appears...
10
by: Rahul Babbar | last post by:
Hi, I am getting the following error, while executing the simple insert script on a few tables. INSERT INTO <table_name>(<col1>) VALUES (1); DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884,...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.