467,135 Members | 1,183 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,135 developers. It's quick & easy.

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
  • viewed: 6829
Share:
2 Replies
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Toonman | last post: by
4 posts views Thread by Viktor Popov | last post: by
3 posts views Thread by othellomy@yahoo.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.