AM Query Error AMM001002 - Conversion failed when converting the varchar value to data type int
search cancel

AM Query Error AMM001002 - Conversion failed when converting the varchar value to data type int

book

Article ID: 404359

calendar_today

Updated On:

Products

CA Client Automation - IT Client Manager CA Client Automation

Issue/Introduction

When creating a AM Query for Software Packages - SW Package... with a Completion Time and operator Days Older :

Following error appears when a preview of the query is done or when the query is executed

Database Error! [AMM001002]

ADO Version 10.0 - COM Error: ErrorCode:-2147217913,WordErrorCode:3079, IDispatch error #3079, Conversion failed when converting the varchar value '(1752501820-864000)' to data type int., Microsoft SQL Server Native Client 11.0, (null)

Same error also occurs for fields "Creation Time" and "Package Created"

Environment

Client Automation 14.5

Cause

Generated SQL Query is like :

ca_discovered_hardware.dis_hw_uuid in (SELECT usd_applic.target FROM usd_applic, usd_actproc, usd_rsw WHERE $(RDS_BITSET4BIN)(usd_applic.task,0) AND usd_applic.actproc=usd_actproc.objectid AND usd_actproc.rsw=usd_rsw.objectid AND usd_applic.status IN (9,29) AND usd_applic.uninstallstate!=2 AND usd_rsw.itemname  =  '7Zip' AND usd_rsw.itemversion  =  '25.0.0' AND usd_actproc.itemname  =  'inst'   AND usd_applic.completiontime  <  '($DATE$-864000)'   )

Problem is here :
 usd_applic.completiontime  <  '($DATE$-864000)' 

If the quote characters are removed it works :
usd_applic.completiontime  <  ($DATE$-864000)

quotes characters are not needed as usd_applic.completiontime is an integer

Resolution

Execute these 4 SQL UPDATE in mdb database :

use mdb

UPDATE RPDATFLD SET flddef=N'Crit2PseudoTemplate=Procedure $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit0PseudoTemplate=Package Name $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit1PossibleValues=select DISTINCT itemversion as label, itemversion as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Crit2ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit2PossibleValues=select DISTINCT itemname as label, itemname as value from usd_actproc where $(RDS_NOTEMPTY)(itemname) order by label;'+CHAR(13)+CHAR(10)+N'Crit7SqlTemplate=AND usd_rsw.creationtime $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Crit9SqlTemplate=AND usd_rsw.$(RDS_SAFENAMEC)(comment) $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'DisplayEnum=;'+CHAR(13)+CHAR(10)+N'Crit0Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Crit3PseudoTemplate=Status = ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit4SqlTemplate=AND usd_applic.administrator $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit6PseudoTemplate=Completion Time $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit7PseudoTemplate=Created $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit7Name=Package Created;'+CHAR(13)+CHAR(10)+N'Crit7Required=0;'+CHAR(13)+CHAR(10)+N'Usage=0;'+CHAR(13)+CHAR(10)+N'CriteriaCount=10;'+CHAR(13)+CHAR(10)+N'Crit6SqlTemplate=AND usd_applic.completiontime $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Crit8Name=Package Supplier;'+CHAR(13)+CHAR(10)+N'Crit1SqlTemplate=AND usd_rsw.itemversion $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit4Name=Ordered By;'+CHAR(13)+CHAR(10)+N'Crit6Name=Completion Time;'+CHAR(13)+CHAR(10)+N'Crit8PseudoTemplate=Supplier $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'PseudoTemplate=($Crit0Pseudo$$Crit1Pseudo$$Crit3Pseudo$$Crit2Pseudo$$Crit4Pseudo$$Crit5Pseudo$$Crit6Pseudo$$Crit7Pseudo$$Crit8Pseudo$$Crit9Pseudo$);'+CHAR(13)+CHAR(10)+N'Crit0Name=Package Name;'+CHAR(13)+CHAR(10)+N'Crit0Required=1;'+CHAR(13)+CHAR(10)+N'Crit2Required=0;'+CHAR(13)+CHAR(10)+N'Crit4PossibleValues=select DISTINCT administrator as label, administrator as value from usd_applic where $(RDS_NOTEMPTY)(administrator) order by label;'+CHAR(13)+CHAR(10)+N'Crit5SqlTemplate=AND usd_applic.creationtime $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Crit0ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit1Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Crit5Required=0;'+CHAR(13)+CHAR(10)+N'Crit5ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Category=Software>Software Packages;'+CHAR(13)+CHAR(10)+N'Table=usd_rsw;'+CHAR(13)+CHAR(10)+N'Crit1PseudoTemplate=Package Version $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit4Required=0;'+CHAR(13)+CHAR(10)+N'' WHERE srcid='ITRM_USD11' AND locid='ENU' AND fldname='Deployment>SW Package...'

UPDATE RPDATFLD SET flddef=cast(cast(flddef AS NVARCHAR(MAX))+cast(N'Crit4ValueMapping=$ORDEREDBY_%CATALOG%=Catalog|$ORDEREDBY_%SYSTEM%=System|$ORDEREDBY_%SWDETECTOR%=Software Detector|;'+CHAR(13)+CHAR(10)+N'Crit5Name=Creation Time;'+CHAR(13)+CHAR(10)+N'Column=itemname;'+CHAR(13)+CHAR(10)+N'Crit0PossibleValues=select DISTINCT itemname as label, itemname as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Crit3Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Crit3ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit7Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'Crit7ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit8ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit1Required=0;'+CHAR(13)+CHAR(10)+N'Crit2Name=Procedure;'+CHAR(13)+CHAR(10)+N'Crit3Name=Status;'+CHAR(13)+CHAR(10)+N'Crit3SqlTemplate=SELECT usd_applic.target FROM usd_applic, usd_actproc, usd_rsw WHERE $(RDS_BITSET4BIN)(usd_applic.task,0) AND usd_applic.actproc=usd_actproc.objectid AND usd_actproc.rsw=usd_rsw.objectid AND usd_applic.status IN ($CritValue$) AND usd_applic.uninstallstate!=2;'+CHAR(13)+CHAR(10)+N'Crit9PossibleValues=select DISTINCT $(RDS_SAFENAMEC)(comment) as label, $(RDS_SAFENAMEC)(comment) as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Name=Deployment > SW Package...;'+CHAR(13)+CHAR(10)+N'Crit2Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Crit3Required=1;'+CHAR(13)+CHAR(10)+N'Crit4ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit5PseudoTemplate=Creation Time $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit7PossibleValues=select DISTINCT creationtime as label, creationtime as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Crit9PseudoTemplate=Comment $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'From=usd_rsw;'+CHAR(13)+CHAR(10)+N'Crit2SqlTemplate=AND usd_actproc.itemname $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit3PossibleValues=Deployed=9,29|Deploying or Deployed=0,1,2,3,4,6,7,8,9,17,18,19,20,21,27,29|Deploying=1,2,3,4,7,8,17,18,19,20,21,27|Deployment failed=10,15,16,28|;'+CHAR(13)+CHAR(10)+N'Crit6Required=0;'+CHAR(13)+CHAR(10)+N'Crit8Required=0;'+CHAR(13)+CHAR(10)+N'Crit8Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Crit8PossibleValues=select DISTINCT supplier as label, supplier as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Crit9Name=Package Comment;'+CHAR(13)+CHAR(10)+N'SqlTemplate=$target.primarykeyfield$ in ($Crit3Sql$ $Crit0Sql$ $Crit1Sql$ $Crit2Sql$ $Crit4Sql$ $Crit5Sql$ $Crit6Sql$ $Crit7Sql$ $Crit8Sql$ $Crit9Sql$);'+CHAR(13)+CHAR(10)+N'Crit0SqlTemplate=AND usd_rsw.itemname $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit5PossibleValues=select DISTINCT creationtime as label, creationtime as value from usd_applic order by label;'+CHAR(13)+CHAR(10)+N'Crit6PossibleValues=select DISTINCT completiontime as label, completiontime as value from usd_applic order by label;'+CHAR(13)+CHAR(10)+N'Crit9Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Crit6ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit9Required=0;'+CHAR(13)+CHAR(10)+N'Crit9ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit1Name=Package Version;'+CHAR(13)+CHAR(10)+N'Crit1ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit8SqlTemplate=AND usd_rsw.supplier $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Where=;'+CHAR(13)+CHAR(10)+N'Crit4Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Crit4PseudoTemplate=Procedure $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit5Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'Crit6Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'ProcessSiblings=0;'+CHAR(13)+CHAR(10)+N'' AS NVARCHAR(MAX))  AS NTEXT) WHERE srcid='ITRM_USD11' AND locid='ENU' AND fldname='Deployment>SW Package...'

UPDATE RPDATFLD SET flddef=N'Crit2PseudoTemplate=Procedure $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit0PseudoTemplate=Package Name $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit1PossibleValues=select DISTINCT itemversion as label, itemversion as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Crit2ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit2PossibleValues=select DISTINCT itemname as label, itemname as value from usd_actproc where $(RDS_NOTEMPTY)(itemname) order by label;'+CHAR(13)+CHAR(10)+N'Crit7SqlTemplate=AND usd_rsw.creationtime $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Crit9SqlTemplate=AND usd_rsw.$(RDS_SAFENAMEC)(comment) $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'DisplayEnum=;'+CHAR(13)+CHAR(10)+N'Crit0Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Crit3PseudoTemplate=Status = ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit4SqlTemplate=AND usd_applic.administrator $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit6PseudoTemplate=Completion Time $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit7PseudoTemplate=Created $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit7Name=Package Created;'+CHAR(13)+CHAR(10)+N'Crit7Required=0;'+CHAR(13)+CHAR(10)+N'Usage=0;'+CHAR(13)+CHAR(10)+N'CriteriaCount=10;'+CHAR(13)+CHAR(10)+N'Crit6SqlTemplate=AND usd_applic.completiontime $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Crit8Name=Package Supplier;'+CHAR(13)+CHAR(10)+N'Crit1SqlTemplate=AND usd_rsw.itemversion $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit4Name=Ordered By;'+CHAR(13)+CHAR(10)+N'Crit6Name=Completion Time;'+CHAR(13)+CHAR(10)+N'Crit8PseudoTemplate=Supplier $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'PseudoTemplate=($Crit0Pseudo$$Crit1Pseudo$$Crit3Pseudo$$Crit2Pseudo$$Crit4Pseudo$$Crit5Pseudo$$Crit6Pseudo$$Crit7Pseudo$$Crit8Pseudo$$Crit9Pseudo$);'+CHAR(13)+CHAR(10)+N'Crit0Name=Package Name;'+CHAR(13)+CHAR(10)+N'Crit0Required=1;'+CHAR(13)+CHAR(10)+N'Crit2Required=0;'+CHAR(13)+CHAR(10)+N'Crit4PossibleValues=select DISTINCT administrator as label, administrator as value from usd_applic where $(RDS_NOTEMPTY)(administrator) order by label;'+CHAR(13)+CHAR(10)+N'Crit5SqlTemplate=AND usd_applic.creationtime $CritOperator$ $CritValue$;'+CHAR(13)+CHAR(10)+N'Crit0ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit1Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Crit5Required=0;'+CHAR(13)+CHAR(10)+N'Crit5ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Category=Software>Software Packages Users Profiles;'+CHAR(13)+CHAR(10)+N'Table=usd_rsw;'+CHAR(13)+CHAR(10)+N'Crit1PseudoTemplate=Package Version $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit4Required=0;'+CHAR(13)+CHAR(10)+N'' WHERE srcid='ITRM_USERPROFILES11' AND locid='ENU' AND fldname='Deployment>SW Package...'

UPDATE RPDATFLD SET flddef=cast(cast(flddef AS NVARCHAR(MAX))+cast(N'Crit4ValueMapping=$ORDEREDBY_%CATALOG%=Catalog|$ORDEREDBY_%SYSTEM%=System|$ORDEREDBY_%SWDETECTOR%=Software Detector|;'+CHAR(13)+CHAR(10)+N'Crit5Name=Creation Time;'+CHAR(13)+CHAR(10)+N'Column=itemname;'+CHAR(13)+CHAR(10)+N'Crit0PossibleValues=select DISTINCT itemname as label, itemname as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Crit3Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Crit3ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit7Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'Crit7ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit8ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit1Required=0;'+CHAR(13)+CHAR(10)+N'Crit2Name=Procedure;'+CHAR(13)+CHAR(10)+N'Crit3Name=Status;'+CHAR(13)+CHAR(10)+N'Crit3SqlTemplate=SELECT usd_applic.target FROM usd_applic, usd_actproc, usd_rsw WHERE $(RDS_BITSET4BIN)(usd_applic.task,0) AND usd_applic.actproc=usd_actproc.objectid AND usd_actproc.rsw=usd_rsw.objectid AND usd_applic.status IN ($CritValue$) AND usd_applic.uninstallstate!=2;'+CHAR(13)+CHAR(10)+N'Crit9PossibleValues=select DISTINCT $(RDS_SAFENAMEC)(comment) as label, $(RDS_SAFENAMEC)(comment) as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Name=Deployment > SW Package...;'+CHAR(13)+CHAR(10)+N'Crit2Type=CT_OBJECT;'+CHAR(13)+CHAR(10)+N'Crit3Required=1;'+CHAR(13)+CHAR(10)+N'Crit4ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit5PseudoTemplate=Creation Time $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit7PossibleValues=select DISTINCT creationtime as label, creationtime as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Crit9PseudoTemplate=Comment $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'From=usd_rsw;'+CHAR(13)+CHAR(10)+N'Crit2SqlTemplate=AND usd_actproc.itemname $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit3PossibleValues=Deployed=9,29|Deploying or Deployed=0,1,2,3,4,6,7,8,9,17,18,19,20,21,27,29|Deploying=1,2,3,4,7,8,17,18,19,20,21,27|Deployment failed=10,15,16,28|;'+CHAR(13)+CHAR(10)+N'Crit6Required=0;'+CHAR(13)+CHAR(10)+N'Crit8Required=0;'+CHAR(13)+CHAR(10)+N'Crit8Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Crit8PossibleValues=select DISTINCT supplier as label, supplier as value from usd_rsw order by label;'+CHAR(13)+CHAR(10)+N'Crit9Name=Package Comment;'+CHAR(13)+CHAR(10)+N'SqlTemplate=$target.primarykeyfield$ in ($Crit3Sql$ $Crit0Sql$ $Crit1Sql$ $Crit2Sql$ $Crit4Sql$ $Crit5Sql$ $Crit6Sql$ $Crit7Sql$ $Crit8Sql$ $Crit9Sql$);'+CHAR(13)+CHAR(10)+N'Crit0SqlTemplate=AND usd_rsw.itemname $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Crit5PossibleValues=select DISTINCT creationtime as label, creationtime as value from usd_applic order by label;'+CHAR(13)+CHAR(10)+N'Crit6PossibleValues=select DISTINCT completiontime as label, completiontime as value from usd_applic order by label;'+CHAR(13)+CHAR(10)+N'Crit9Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Crit6ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit9Required=0;'+CHAR(13)+CHAR(10)+N'Crit9ValueSelector=VS_EDITABLELIST;'+CHAR(13)+CHAR(10)+N'Crit1Name=Package Version;'+CHAR(13)+CHAR(10)+N'Crit1ValueSelector=VS_LIST;'+CHAR(13)+CHAR(10)+N'Crit8SqlTemplate=AND usd_rsw.supplier $CritOperator$ ''$CritValue$'';'+CHAR(13)+CHAR(10)+N'Where=;'+CHAR(13)+CHAR(10)+N'Crit4Type=CT_STRING;'+CHAR(13)+CHAR(10)+N'Crit4PseudoTemplate=Procedure $CritOperator$ ''$CritValueLabel$'' ;'+CHAR(13)+CHAR(10)+N'Crit5Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'Crit6Type=CT_DATE;'+CHAR(13)+CHAR(10)+N'ProcessSiblings=0;'+CHAR(13)+CHAR(10)+N'' AS NVARCHAR(MAX))  AS NTEXT) WHERE srcid='ITRM_USERPROFILES11' AND locid='ENU' AND fldname='Deployment>SW Package...'

 

And open a new DSM Explorer.

Additional Information

Workaround if above solution could not be applied 

  1. Right click on the argurment and select Edit SQL...


  2. In SQL View, go to the end and remove the quote characters around ($DATE$-xxxxx)

    Example :
    Replace 
    usd_applic.completiontime  <  '($DATE$-1728000)'   )
    by
    usd_applic.completiontime  <  ($DATE$-1728000)   )

  3. Click OK button to save the change.
Powered by Wolken Software