
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-30-2015 09:37 PM
I was messing around with the asset out of warranty report and screwed something up. Now I keep getting the error "error while getting report Data conversion failed. [ OLE DB status value (if known) = 2 ]"
Here is my code
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblOperatingsystem.Caption,
tblADusers.Department,
tsysIPLocations.IPLocation,
tblAssets.IPAddress
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
And tsysAssetTypes.AssetTypeIcon48 = tblAssets.Assettype And
tsysAssetTypes.AssetTypeIcon10 = tblAssetCustom.FTPheader
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And
tsysAssetTypes.AssetTypeIcon48 = tblADusers.IpPhone
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblAssetCustom.Warrantydate < GetDate()
Order By [Warranty Expiration] Desc
Here is my code
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblOperatingsystem.Caption,
tblADusers.Department,
tsysIPLocations.IPLocation,
tblAssets.IPAddress
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
And tsysAssetTypes.AssetTypeIcon48 = tblAssets.Assettype And
tsysAssetTypes.AssetTypeIcon10 = tblAssetCustom.FTPheader
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And
tsysAssetTypes.AssetTypeIcon48 = tblADusers.IpPhone
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblAssetCustom.Warrantydate < GetDate()
Order By [Warranty Expiration] Desc
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-31-2015 09:24 PM
Here is the original report if you need to have that to start from:
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc
3 REPLIES 3
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-01-2015 02:55 PM
MR1371 wrote:
I was messing around with the asset out of warranty report and screwed something up. Now I keep getting the error "error while getting report Data conversion failed. [ OLE DB status value (if known) = 2 ]"
Your report contains several incorrect Joins. TsysAssetTypes.AssetTypeIcon48 and tblADusers.IpPhone are linked for instance, but these fields have nothing to do with each other. We advise anyone to read up on SQL before attempting to build or modify reports, as the Lansweeper report builder is a standard SQL editor. Understanding Joins and other SQL principles is a must. All built-in SQL reports can be found in our report center.
rhodges wrote:
Unfortunately, I don't know what IPLocations are and how to link the tsysIPLocations to the working tables.
IP locations are groups of IP addresses you can define in the Configuration\Asset Groups section of the console. To add this information to a report, follow the instructions in this forum topic.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-31-2015 10:07 PM
I created a report with the information that I thought you were looking for. Unfortunately, I don't know what IPLocations are and how to link the tsysIPLocations to the working tables. This is what I have so far:
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblADusers.Username,
tblOperatingsystem.Caption,
tblADusers.Department,
tblAssets.IPAddress,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.OScode
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.Warrantydate < GetDate()
Order By [Warranty Expiration] Desc
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblADusers.Username,
tblOperatingsystem.Caption,
tblADusers.Department,
tblAssets.IPAddress,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.OScode
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.Warrantydate < GetDate()
Order By [Warranty Expiration] Desc

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-31-2015 09:24 PM
Here is the original report if you need to have that to start from:
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc
Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc
