cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Data Warehouse Calculation fails

Does anyone know, what to do if the Data Warehouse Calculation fails every night?

0 Kudos
5 Replies
Level 11

If you use the latest release, yes. It's a faulty t-sql query.

[ 17478|armserveserver |armServer |7904.1|195|domain\account |pn.database.msSql.M…lDataBaseTransaction|200709|20:01:50.834|Error ] DataBaseHelper.HandleDataBaseSqlException(Object source, SqlException exception, String sqlCommand): System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
--BEGIN-OF-EXCEPTION------------------------------------------------------------
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
at pn.database.msSql.MsGenericDataBaseTransaction`2.ExecuteSqlCommandWithResults(String sqlCommand, SqlParameter[] parameterList)
ClientConnectionId:348ff6a7-a562-46a5-8114-f851f2503557
Error Number:241,State:1,Class:16
Additional infos that might be helpful on research: ErrorNumber: 241, ErrorClass: 16, ErrorState: 1, ErrorCode: -2146232060
--END-OF-EXCEPTION--------------------------------------------------------------
[ 17479|armserveserver |armServer |7904.1|195|domain\account |pn.database.msSql.M…lDataBaseTransaction|200709|20:01:50.834|Error ] DataBaseHelper.HandleDataBaseSqlException(Object source, SqlException exception, String sqlCommand): ;WITH details AS(
SELECT XXXX as _scan_id, e._entry_id AS _resource_id,
COALESCE(e._display_name, e._name, e._account_name) AS _resource_name,
e._path AS _resource_path,
_sid AS _account_id,
_guid AS _account_guid,
CONVERT(datetime, CASE WHEN PATINDEX('__/__/160_ __:__:__', ep._value) != 0 THEN '' ELSE ISNULL(ep._value_big, ep._value) END, 101) AS _timestamp,
DATEDIFF(DAY,CONVERT(datetime, CASE WHEN PATINDEX('__/__/160_ __:__:__', ep._value) != 0 THEN '' ELSE ISNULL(ep._value_big, ep._value) END, 101),GETUTCDATE()) AS _daysAgo,
CASE WHEN e._user_account_control & 2 != 2 THEN 1 ELSE 0 END AS _isEnabled
FROM ad._active_directory_entries_XXXX e
INNER JOIN ad._active_directory_entry_property_mapping_XXXX m ON m.[_entry_id] = e.[_entry_id]
INNER JOIN ad._active_directory_entry_properties_XXXX ep ON m.[_property_id] = ep.[_property_id]
WHERE e._object_class = 6 AND ep._name_id = (SELECT [_property_name_id] FROM ad._active_directory_property_names_XXXX WHERE _property_name = 'lastlogontimestamp'))
INSERT INTO [dw].[details_533080ab41ec4e9399eab0a7aa546c4f] ([_scan_id],[_resource_id],[_resource_name],[_resource_path],[_provider],[_sid], [_guid], [_lastLogon],[_daysAgo],[_is_enabled])
SELECT _scan_id, _resource_id,_resource_name,_resource_path,'domain.de', _account_id, _account_guid, _timestamp,_daysAgo, _isEnabled
FROM details
WHERE _daysAgo>=31;
SELECT COUNT(*) AS _count FROM [dw].[details_533080ab41ec4e9399eab0a7aa546c4f];

0 Kudos
Level 8

Hello,

what version of ARM are you using?

We had the same problem with version 2020.2 and also 2020.2.1 and opened a support ticket.

With a development version for testing, the error was gone, so i think it will be fixed with the next upcoming version.

 

Same here.

 

It's a bug in the current 2020.2 version. The DevVersion they sent out to me has the fix.

0 Kudos
Level 10

any details from the logs? check out the armServer.log for errors at the time of the execution (should be visible in the job overview)

0 Kudos

It started to fail after the update to Version 2020.2.1 

The armserver.log is really big (how big should it normally be?) and I see only confusing messages 🙂 but I'll try to find something ...

I opened a support case, too, like @cnfloege  🙂 hope they will come back to me

thx for your help!

0 Kudos