→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
BartCl
Engaged Sweeper
Hi,

When i try to run the following report i get the error message "Error converting data type varchar to numeric"

this is the querry i try to run.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Serialnumber,
tsysIPLocations.StartIP,
tsysIPLocations.EndIP
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
tsysIPLocations
Where tblAssetCustom.State = 1 And tsysIPLocations.StartIP = '10.136.112.1' And
tsysIPLocations.EndIP = '10.136.113.255'

Can anybody help me with this issue?

Thank you.
1 ACCEPTED SOLUTION
Klas
Engaged Sweeper II
you have to join the table "tsysIPLocations" properly. Change this part: "...= tblAssets.Assettype,
tsysIPLocations..."
to = "... =tblAssets.Assettype
join tsysIPLocations on tblAssets.IPAddress >= tsysIPLocations.Realstart and tblAssets.IPAddress <= tsysIPLocations.Realend

you don't have to; but this done, you can then change your where-clause; You probably given all your different ip-ranges a name? Let's say yours (10.136.112. -- 10.136.113.255) is called "BartsIPlocation". Then change it to "... Where tsysIPLocations.IPLocation= 'BartsIPlocation' And tblAssetCustom.State = 1 "

View solution in original post

6 REPLIES 6
BartCl
Engaged Sweeper
Klas,

thank you for your help, seemd to be working now.

Kind regards
Klas
Engaged Sweeper II
remove the comma after tblAssets.Assettype - should look like:

".. Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
join tsysIPLocations on tblAssets.IPAddress >= tsysIPLocations.Realstart and tblAssets.IPAddress <= .."
BartCl
Engaged Sweeper
Hi Klas,

I just made the changes like the following and I get a syntax error:

Select Top 100 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Serialnumber,
tsysIPLocations.Realstart,
tsysIPLocations.Realend
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
join tsysIPLocations on tblAssets.IPAddress >= tsysIPLocations.Realstart and tblAssets.IPAddress <= tsysIPLocations.Realend
Where tsysIPLocations.IPLocation= 'Namen' And tblAssetCustom.State = 1


Sorry for the inconvienence but my sql isn't that good 😉
Klas
Engaged Sweeper II
you have to join the table "tsysIPLocations" properly. Change this part: "...= tblAssets.Assettype,
tsysIPLocations..."
to = "... =tblAssets.Assettype
join tsysIPLocations on tblAssets.IPAddress >= tsysIPLocations.Realstart and tblAssets.IPAddress <= tsysIPLocations.Realend

you don't have to; but this done, you can then change your where-clause; You probably given all your different ip-ranges a name? Let's say yours (10.136.112. -- 10.136.113.255) is called "BartsIPlocation". Then change it to "... Where tsysIPLocations.IPLocation= 'BartsIPlocation' And tblAssetCustom.State = 1 "
BartCl
Engaged Sweeper
Hi Klas,

Thank you seems to solve my problem 😉

but when I run this query I still get al the machines in the network and not only the one between the ip ranges?

Do you have any idea what causes this.

I adjusted the query into:

Select Top 100 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Username,
tblAssetCustom.Serialnumber,
tsysIPLocations.Realstart,
tsysIPLocations.Realend
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
tsysIPLocations
Where tsysIPLocations.Realstart = '10.136.112.1' And tsysIPLocations.Realend =
'10.136.113.255' And tblAssetCustom.State = 1
Klas
Engaged Sweeper II
Hi BartCl,
Column StartIP and EndIP are both of data type "numeric". Therefore you can't have a where-clause providing a char-value. Those fields holds ip-values by A) replace blanks with zeroes B) remove any ".". I.e your startIP should look like this instead; 10136112001.
Or; there are two corresponding char-based fields in the table called "realstart" and "realend". They hold the ip-address in "normal" format.
So there are two alternatives:
1) ... where tblAssetCustom.State = 1 And tsysIPLocations.StartIP = 10136112001 And
tsysIPLocations.EndIP = 10136113255
or
2)... where tblAssetCustom.State = 1 And tsysIPLocations.realstart = '10.136.112.1' And
tsysIPLocations.realend = '10.136.113.255'