locked
Configuration Manager; Date & Time Based Query Issue RRS feed

  • Question

  • Hi,

    I'm having an issue with a collection query in CM CB 1802.

    The queries in the past have worked OK and indeed those previous queries still work now.

    When I tried to replicate the query on a new collection, the results are 0 and SCCM reports errors in Site Status.

    The previous query:

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PC_BIOS.SMBIOSBIOSVersion != "K01 v03.07" and SMS_G_System_PC_BIOS.ReleaseDate < "10/05/2018 00:00:00" and SMS_G_System_COMPUTER_SYSTEM.Model in ("HP Compaq Elite 8300 SFF","HP Compaq Elite 8300 SFF PC ALL","HP COMPAQ ELITE 8300 SMALL FORM FACTOR","HP Elite 8300 SFF PC")


    The new query:

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PC_BIOS.SMBIOSBIOSVersion != "L04 v02.31" and SMS_G_System_PC_BIOS.ReleaseDate < "18/05/2018 01:00:00" and SMS_G_System_COMPUTER_SYSTEM.Model = "HP EliteDesk 800 G1 DM"


    The error:

    Error

    Milestone

    SMS_COLLECTION_EVALUATOR_SINGLE_EVALUATOR

    Microsoft SQL Server reported SQL message 242, severity 16: [22007][242][Microsoft][SQL Server Native Client 11.0][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.    Please refer to your Configuration Manager documentation, SQL Server documentation, or the Microsoft Knowledge Base for further troubleshooting information.

    Note:

    We're in the UK so the visible date format is dd/mm/yyyy.

    I've opened the the same views in SQL Management Studio for the CM DB and none of the returned values with the same query have any issues.  The date format in the results is yyyy-mm-dd.  If I manually change this on the collection WQL query so the date format is yyyy-mm-dd, it all works OK.

    P.S. I used the query designer and selected the values presented to me, just as with the previous query so shouldn't be a typo issue in the query language.

    The problem:

    So why do previous queries work, yet new ones do not?  

    Cheers.

    Tuesday, November 13, 2018 3:20 PM

All replies

  • Hi Fahid S,

    It's weird, I created these two device collections in my lab with your rules,because it doesn't match my devices in my lab, so the Member count is 0, but no error is found.

    Did you using the same user account when creating the two device collections?

    Did you see this link?
    Https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in

    To check or change it in SSMS go to Security -> Logins and right-click the username of the user that runs the queries. Select properties -> general and make sure the default language at the bottom of the dialog is what you expect.
    Repeat this for all users that run queries.

    Best regards,

    Yuxiang


    Please remember to mark the replies as answers if they help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Wednesday, November 14, 2018 5:24 AM
  • Hi Yuxiang,

    Thanks for your reply and sorry for the delayed response.

    So, yes the same account was used to create the first query as was the second and additional.

    The accounts are given permissions via a Security Group.  The said Sec Group has no default language set in SQL.

    However, this has been same always, so why did the first query work but not the second?

    Also looking at StackOverflow link you posted, yes if run the query on SQL, the CONVERT option is automatically added.  But you can't add that to the WQL query.  Also it still doesn't explain how the first query worked.

    Thanks.

    Fahid

    Monday, November 19, 2018 11:50 AM
  • Further update on this.

    I copied the query language from the collection and ran it as a fresh query in 'Monitoring'.  The query works and shows the expected results.

    I also looked at the SMSProv.log and it shows the following:

    ERROR>: CSspQueryForObject::ConvertWBEMTimeToDBTime invalid wbemtime [10/05/2018 00:00:00] SMS Provider 19/11/2018 11:48:40 22008 (0x55F8)

    Execute WQL  =select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_PC_BIOS.SMBIOSBIOSVersion != "K01 v03.07" and SMS_G_System_PC_BIOS.ReleaseDate < "10/05/2018 00:00:00" and SMS_G_System_COMPUTER_SYSTEM.Model in ("HP Compaq Elite 8300 SFF","HP Compaq Elite 8300 SFF PC ALL","HP COMPAQ ELITE 8300 SMALL FORM FACTOR","HP Elite 8300 SFF PC")	SMS Provider	19/11/2018 11:48:40	22008 (0x55F8)
    
    Execute SQL =select  all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System INNER JOIN PC_BIOS_DATA AS SMS_G_System_PC_BIOS ON SMS_G_System_PC_BIOS.MachineID = SMS_R_System.ItemKey  INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey   where ((SMS_G_System_PC_BIOS.SMBIOSBIOSVersion00 <> N'K01 v03.07' AND SMS_G_System_PC_BIOS.ReleaseDate00 < '10/05/2018 00:00:00') AND SMS_G_System_COMPUTER_SYSTEM.Model00 in (N'HP Compaq Elite 8300 SFF',N'HP Compaq Elite 8300 SFF PC ALL',N'HP COMPAQ ELITE 8300 SMALL FORM FACTOR',N'HP Elite 8300 SFF PC'))	SMS Provider	19/11/2018 11:48:40	22008 (0x55F8)
    

    Cheers.
    Monday, November 19, 2018 12:04 PM