Closed matteu31400 closed 3 years ago
Hi @matteu31400,
my guess would be some not expected datetime values in the QUICK_FIX_ENGINEERING_DATA table, since that is the only query which is using a conversion from nvarchar to varbinary. The report will run the following query, which you can run in SQL directly and which should also output the same error:
Select QFE.ResourceID ,LastInstallTime = max(CASE WHEN (ISDATE(QFE.InstalledOn0) = 0 and QFE.InstalledOn0 != '') THEN CAST((CONVERT(BIGINT,CONVERT(VARBINARY(64), '0x' + QFE.InstalledOn0,1)) / 864000000000.0 - 109207) AS DATETIME) WHEN QFE.InstalledOn0 = '' THEN '01.01.1999 00:00:00' ELSE QFE.InstalledOn0 END) from v_GS_QUICK_FIX_ENGINEERING QFE where (QFE.Description0 = 'Security Update' or QFE.Description0 is null) group by QFE.ResourceID
if that's the case, could you please run the following query and send me the output?
I will then try to fix the query to take the different data format into account.
select QFE.InstalledOn0 from v_GS_QUICK_FIX_ENGINEERING QFE where ISDATE(QFE.InstalledOn0) = 0 and QFE.InstalledOn0 != ''
Best regards Jonas
Hello,
Thanks for your answer. If I launch the SQL query, I have the same error yes.
This is the result I have with your second querry :
12/15/2017 4/19/2018 2/17/2019 2/27/2020 5/15/2019 11/14/2019 4/15/2020 6/16/2020 6/16/2020 12/15/2017 4/27/2018 2/16/2019 2/27/2020 5/15/2019 11/13/2019 2/13/2020 4/15/2020 6/16/2020 7/15/2020 7/15/2020 5/28/2019 11/28/2017 12/15/2017 12/15/2017 9/19/2018 6/23/2020 6/23/2020 6/23/2020 6/23/2020 6/23/2020 11/28/2017 2/17/2019 2/27/2020 5/15/2019 11/13/2019 4/15/2020 6/16/2020 7/15/2020 7/15/2020 11/29/2017 4/27/2018 3/14/2019 5/15/2019 6/13/2019 11/14/2019 6/23/2020 6/23/2020 6/23/2020 12/15/2017 3/19/2019 9/16/2019 8/30/2019 6/23/2020 6/22/2020 3/18/2014 3/18/2014 3/18/2014 3/18/2014 12/15/2017 12/15/2017 12/15/2017 1/20/2019 1/20/2019 3/13/2019 3/13/2019 4/25/2019 4/25/2019 5/15/2019 5/15/2019 7/15/2019 7/15/2019 7/15/2019 8/14/2019 8/14/2019 8/17/2019 11/14/2019 11/14/2019 11/14/2019 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/23/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 6/24/2020 3/18/2014 3/18/2014 3/18/2014 3/18/2014 12/15/2017 12/15/2017 12/15/2017 12/15/2017 12/15/2017 12/15/2017 12/15/2017 12/15/2017 11/18/2017 12/15/2017 12/15/2017 12/15/2017 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 9/18/2019 12/15/2017 4/27/2018 9/16/2019 5/20/2019 8/29/2019 9/16/2019 4/27/2018 6/23/2020 6/23/2020 6/23/2020 6/23/2020 6/23/2020 6/23/2020 6/27/2020 6/29/2020 6/29/2020 6/27/2020 6/29/2020
Hi @matteu31400,
this can happen if the SQL server output language is not set to us_english which is the default language.
You can run the following query to output the current language in use:
select @@LANGUAGE
Unfortunately the "ISDATE" function depends on the output language set in SQL and can output either 0 or 1 for the date format dd/mm/yyy and the date "6/23/2020" which will break the query in that case. I am thinking about a general fix, but changing the output language to us_english should fix that in the meantime.
Hope this helps!
Best regards Jonas
Hi,
Sorry for the late answer.
I’m in french language ☹
Can I do some change for this to work or not ?
Thank you
Hi @matteu31400 I found time to fix the issue in the SQL query. Just download the latest version of this repository and the report should work fine.
Hello,
I test the update but I always have an error when I launch the report "Software updates compliance - overview"
In french
Une erreur s'est produite lors du traitement du rapport. (rsProcessingAborted) L'exécution a échoué pour le jeu de données partagé « ClientSecurityOverview ». (rsDataSetExecutionError) Impossible de lire la ligne de données suivante pour le dataset UpdatesSummary. (rsErrorReadingNextDataRow) La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites. | Une erreur s'est produite lors du traitement du rapport. (rsProcessingAborted) L'exécution a échoué pour le jeu de données partagé « ClientSecurityOverview ». (rsDataSetExecutionError) Impossible de lire la ligne de données suivante pour le dataset UpdatesSummary. (rsErrorReadingNextDataRow) La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites.
last line says : convert data type from nvarchar to datatype datetime create value out of limit.
Found some time to rewrite the query. Feel free to download the latest files and test again.
Hello,
With this last version, it seems ok for now :)
Hello,
Thanks for this report however I have an issue. Yesterday I installed it and it worked perfectly. Now, I can't run it again I have this error :
An error occurred while processing the report. (rsProcessingAborted) Execution failed for the shared dataset "ClientSecurityOverview". (rsDataSetExecutionError) The following data row for the UpdatesSummary dataset cannot be read. (rsErrorReadingNextDataRow) Error converting nvarchar data type to varbinary.
I don't know how to build report but I know how to import it. I already tried to delete folder in SSRS website + create it again and launch script but the issue persists :(
Thanks for your answer.