Home› Service Manager

sql Jobs failing

Veljko_SeroVeljko_Sero Customer IT Monkey ✭
in Service Manager

Hi,

Since I updated Cireson portal my SQL guys getting alerts that SQL jobs are failing.

SQL Server Instance DCSQLP03-17\SCSMPRD - SQL Agent job [Cireson Insert New Manual Activity] completed with a status of 'Failed' at 3/14/2017 7:40:38 AM.

                The most recent run of this job started at 3/14/2017 7:40:37 AM and completed with the message 'The job failed.  The Job was invoked by Schedule 20 (Insert New Manual Activities).  The last step to run was step 1 (Insert New Manual Activities).'.

                Since 3/14/2017 7:40:37 AM this job had the following statistics.

                                Completed successfully 0 times,

                                Retried 0 times,

                                Failed 1 times,

                                Canceled 0 times.

 

SQL Server Agent Job Completion: The SQL Server Agent job/step has completed.

Cireson Insert new changes, Insert New Manual activity and Insert New Services failing every day. Cireson Update Existing Display string fails today.

I do not see it affects users, at least I have not heard anything.  Can anyone provide any information what is this about before I create a ticket and What do I supposed to check .

Answers

  • Veljko_SeroVeljko_Sero Customer IT Monkey ✭

    so I run those scripts manually and it appears it is failing , at least what I can see,  because priority ID is blank. I could be wrong. If this is the case why is that field mandatory if you can create change  or activity that and remove priority or leave it blank.

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    I would open a ticket with Support.
    But for me those jobs are running successfully. However what I have seen is some places in the database where there is a blank value vs null value. I find this is happening when the portal has the drop down issue where the disappears when scrolled to the bottom of the page. (https://support.cireson.com/Problem/Edit/PR62338/) causing some of my left joins to fail in queries. 
  • merlenette_jonesmerlenette_jones Member Advanced IT Monkey ✭✭✭
    Hello,


    We are aware of the issue and are looking into it.


    I will have a PR available to view for updates soon.
  • john_doylejohn_doyle Cireson Support Ninja IT Monkey ✭✭✭✭
    The problem is likely to be the PriorityId as suggested by @Veljko_Sero.

    The PriorityId field in the WorkItem table is a character field as it has to store integers for the Incident class and guids for the other classes. The SQL Jobs which insert and update non-incident classes assume that the PriorityId contains a valid guid or NULL, but that is not certain.

    I would suggest opening SQL Management Studio, and browsing down to the SQL Server Agent > Jobs. Right-click one of the jobs which is failing and select Script job as > DROP and CREATE to > New query window

    Find the join:
      left join [CiresonSM].[dbo].DisplayString PriorityDS
      on S.PriorityId = PriorityDS.ElementID and PriorityDS.LocaleID = @locale

    change this to 
    left join [CiresonSM].[dbo].DisplayString PriorityDS
      on TRY_CAST(wi.PriorityId as uniqueidentifier) = PriorityDS.ElementID and PriorityDS.LocaleID = @locale

    Execute that and try to run the job again.
  • Stuart_CuthbertsonStuart_Cuthbertson Customer IT Monkey ✭
    edited January 2018
    @john_doyle My organization is experiencing the same problem - definitely due to the mix of int and uniqueidentifier data (and some empty strings).

    Since this is quite an old thread (but the only one I can find on the topic), do you know if manually editing the SQL jobs, as you describe, is still the recommended solution? 

    Thanks in advance!
  • john_doylejohn_doyle Cireson Support Ninja IT Monkey ✭✭✭✭
    @Stuart_Cuthbertson If you are on the latest release and it is the Update Existing work item jobs which are failing then the issue is slightly different. You can correct it manually by dropping and creating the jobs. If you scroll down to the joins with the DisplayString table you will find that the query is joining the SM_WorkItem_XXX table to the DisplayString table. This is incorrect. You need to replace that table name with the alias of the WorkItem table. The joins look like this:
    &nbsp;&nbsp;&nbsp; left join [ServiceManagement].[dbo].DisplayString StatusDS<br>&nbsp;&nbsp;&nbsp; on [SM_WorkItem_ServiceRequest].[StatusId] = StatusDS.ElementID and StatusDS.LocaleID = ISNULL(@locale, ''ENU'')
    but they should look like this:
    &nbsp;&nbsp;&nbsp; left join [ServiceManagement].[dbo].DisplayString StatusDS<br>&nbsp;&nbsp;&nbsp; on WI.[StatusId] = StatusDS.ElementID and StatusDS.LocaleID = ISNULL(@locale, ''ENU'')

    This should be corrected in the next release which should be available very soon. It may not be worth your while editing it now.



  • Stuart_CuthbertsonStuart_Cuthbertson Customer IT Monkey ✭
    Thanks for the clarification @john_doyle. We are on v8.2.0.2012 (versions.json says 820). I am not clear if that is the very latest release as 8.2.* isn't listed at all on this KA: https://support.cireson.com/KnowledgeBase/View/94#/.

    Could you just confirm (if known) what the version number will be of the release that fixes this problem, so we can look out for it? 
Sign In or Register to comment.