MS SQL auto archive for ESEC partitions fails

  • 7001256
  • 01-Sep-2008
  • 26-Apr-2012

Environment

Novell Sentinel 6.0 Support Pack 2
Microsoft Windows Server 2003 Enterprise Edition

Situation

- MS SQL auto-archive job for backing up esec_offline_partitions fails.
- However, manual backup through Sentinel Data Manager works.
- Windows Event log shows following errors:

ERROR:
SQL Server Scheduled Job 'SentinelOfflinePartitions - EVENTS' (0x63C40785F2695549BB297FF303B2A901) - Status: Failed - Invoked on: 2008-07-02 03:00:00 - Message: The job failed. The Job was invoked by Schedule 3 (SentinelOfflinePartitions-EVENTS). The last step to run was step 1 (Call the esec_offline_partitions).

ERROR: SQL Server Scheduled Job 'SentinelOfflinePartitions - EVENT_SMRY' (0x28F8CCCC066D83419E49D120545A1240) - Status: Failed - Invoked on: 2008-07-02 05:00:00 - Message: The job failed. The Job was invoked by Schedule 4 (SentinelOfflinePartitions-EVENT_SMRY). The last step to run was step 1 (Call the esec_offline_partitions).

Resolution

Check the language settings of the System. The scheduled MS SQL Job to backup offline partitions is done by the "System" user, whereas the default Sentinel MS SQL DB language is English.

If there is a mismatch between the 2 settings, the auto archive operation will fail because different date formats are used for the respective languages and the SQL database does not interpret the differing date format correctly. The manual archive process works fine because it uses the environment of the "esecadm" user, with the default language setting of English and avoids the mismatch stated above.

If the language of the system is set to anything other than English (German, for instance), change the 'System' default language to English to resolve this issue.

Additional Information

SQL Server Agent Job history for the failed job shows the following:

Date        25.08.2008 03:00:01
Log     Job History (SentinelOfflinePartitions - EVENTS)

Step ID     0
Server      SIEM01COL
Job Name        SentinelOfflinePartitions - EVENTS
Step Name       (Auftragsergebnis)
Duration        00:00:00
Sql Severity        0
Sql Message ID      0
Operator Emailed       
Operator Net sent      
Operator Paged     
Retries Attempted       0

Message
The job failed.  The Job was invoked by Schedule 3 (SentinelOfflinePartitions-EVENTS).  The last step to run was step 1 (Call the esec_offline_partitions).

------------------------------------------------------------------------------------
Date        25.08.2008 03:00:01
Log     Job History (SentinelOfflinePartitions - EVENTS)

Step ID     1
Server      SIEM01COL
Job Name        SentinelOfflinePartitions - EVENTS
Step Name       Call the esec_offline_partitions
Duration        00:00:00
Sql Severity        16
Sql Message ID      50000
Operator Emailed       
Operator Net sent      
Operator Paged     
Retries Attempted       0

Message
Executed as user: NT AUTHORITY\SYSTEM. CORRELATED_EVENTS: Error dropping partition CORRELATED_EVENTS_P_20080409130000 - SEN-30010 [SQLSTATE 42000] (Error 50000).  The step failed.



===========================================================================================================
Date        25.08.2008 05:00:00
Log     Job History (SentinelOfflinePartitions - EVENT_SMRY)

Step ID     0
Server      SIEM01COL
Job Name        SentinelOfflinePartitions - EVENT_SMRY
Step Name       (Auftragsergebnis)
Duration        00:00:00
Sql Severity        0
Sql Message ID      0
Operator Emailed       
Operator Net sent      
Operator Paged     
Retries Attempted       0

Message
The job failed.  The Job was invoked by Schedule 4 (SentinelOfflinePartitions-EVENT_SMRY).  The last step to run was step 1 (Call the esec_offline_partitions).

---------------------------------------------------------------------------------------
Date        25.08.2008 05:00:00
Log     Job History (SentinelOfflinePartitions - EVENT_SMRY)

Step ID     1
Server      SIEM01COL
Job Name        SentinelOfflinePartitions - EVENT_SMRY
Step Name       Call the esec_offline_partitions
Duration        00:00:00
Sql Severity        16
Sql Message ID      50000
Operator Emailed       
Operator Net sent      
Operator Paged     
Retries Attempted       0

Message
Executed as user: NT AUTHORITY\SYSTEM. EVT_DEST_EVT_NAME_SMRY_1: Error dropping partition EVT_DEST_EVT_NAME_SMRY_1_P_20080131130000 - Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerh [SQLSTATE 42000] (Error 50000).  The step failed.



The error message above states (in German) that there is a problem with the date conversion because a value is out of range. If the system language is set to use a language apart from German and English, the error message above will be displayed in the appropriate system language. The issue resolution, however, remains obviously the same in these cases too.