Latin1_General_CI_AI - error queries with lower (ntext)

[j]

This question is in reference to Atlassian Documentation: Connecting JIRA applications to SQL Server 2008

We use JSD and actually we try to generate SLA using JQL and filter request types. All seems ok, i can register the SLA, but it doesnt work, when i review the log files i find:

 Caused by: java.sql.SQLException: Argument data type ntext is invalid for argument 1 of lower function.

and above the query that fails, the error is with lower(ntext) 

how can i solve this error?

select top (?)
"AO_54307E_VIEWPORTFORM"."CALL_TO_ACTION",
"AO_54307E_VIEWPORTFORM"."DESCRIPTION",
"AO_54307E_VIEWPORTFORM"."FORM_ORDER",
"AO_54307E_VIEWPORTFORM"."ICON",
"AO_54307E_VIEWPORTFORM"."ICON_ID",
"AO_54307E_VIEWPORTFORM"."ID",
"AO_54307E_VIEWPORTFORM"."INTRO",
"AO_54307E_VIEWPORTFORM"."ISSUE_TYPE_ID",
"AO_54307E_VIEWPORTFORM"."KEY",
"AO_54307E_VIEWPORTFORM"."NAME",
"AO_54307E_VIEWPORTFORM"."VIEWPORT_ID"
from
"dbo"."AO_54307E_VIEWPORTFORM" "AO_54307E_VIEWPORTFORM"
inner join "dbo"."AO_54307E_VIEWPORT" "AO_54307E_VIEWPORT"
on "AO_54307E_VIEWPORTFORM"."VIEWPORT_ID" = "AO_54307E_VIEWPORT"."ID"
where
"AO_54307E_VIEWPORTFORM"."VIEWPORT_ID" = ?
and (lower("AO_54307E_VIEWPORTFORM"."NAME") like ? escape '\'
or (lower("AO_54307E_VIEWPORTFORM"."DESCRIPTION") like ? escape '\')
or (lower("AO_54307E_VIEWPORTFORM"."INTRO") like ? escape '\')
and ("AO_54307E_VIEWPORTFORM"."ISSUE_TYPE_ID" in (select cast("issuetype"."ID" as bigint)
from "dbo"."issuetype" "issuetype")))
order by "AO_54307E_VIEWPORT"."NAME" asc, "AO_54307E_VIEWPORTFORM"."FORM_ORDER" asc

[j]

i registered a support ticket

https://support.atlassian.com/servicedesk/customer/portal/3/SDS-18010

[Jack Brickey]

Thanks for all the great details. I certainly appears that SLAs do not like "Customer Request Type". I just tried a quick test of my own and observed the same thing - no SLA shown when using Customer Request Type. My suggestion at this point is to open a support request w/ Atlassian. I would think that this should work. I can certainly see where having different SLAs based upon request type would be a valid/desirable use case. I may play around w/ it some more and if I find anything I will respond back. If you open a support ticket please update here so the community can benefit.

cheers,

jack

[j]

the issuetype of the customer request type "backup" is "Operations (RFSO)"

i paste the image of the same register in each case

[j]

When i execute the filter from Issues->Search it works perfectly!

Sin título2.png

but, i copied the query in sla definition

Sin título3.png

and when i register a new issue the SLA does not apply (in the image the sla that i defined does  not appear)

Sin título4.png

but if i change the sla for filter based on other field x example issue type

Sin título5.png

the sla works fine!

Sin título6.png

[Jack Brickey]

Jaguilar,

sorry for delay I was busy on other things. Can you try your JQL w/in the Issues>Search for Issues. I'm wondering if your JQL might incorrect. If you can get the search to work then try copy+paste into SLA JQL.

[j]

Sin título1.png

[j]

Hello Jack,

the workflow doesnt transition to inprogress after register, that is not the problem

if i change the JQL to

"Issue Type"=Incident

 the SLA works correctly (i mean i can see the SLA in detail of the issue, the time is calculated propertly) but if i write in JQL 

"Customer Request Type" = Backup

the SLA is NOT visible, so doesnt work

[Jack Brickey]

thanks for the screenshot. It all appears fine though i do question the stop on "in progress". By using this status to stop the timer, I assume what you are trying to measure here is the time a request stays in the "waiting for support" queue (inbox) before an agent moves it to in progress rather than a time to resolve? Of course if the agents always move requests to "in progress" then the other stop events have not impact since the SLA completes and there is no additional Start events that would restart it. It seems like this SLA is more of a Time to First Response SLA which exists natively. With that observation aside, i'm scratching my head so lets back up. When you say it is not working, how are you concluding this? Other than the logs what are you seeing on your view screens? Do you not see the SLA by name show up when an issue is created? screenshot of what you are or are not seeing on the view issue screen might help. Finally, how are you concluding that the exception is related to the SLA?

[j]

Sin título.png

[Jack Brickey]

To be clear are you using the SLAs screen to setup your SLA? If so I wonder if you could provide a snapshot of the screen. If not can you tell me what you have for Start/Pause on/Stop? Then I assume the Issues (JQL) box has the "Customer Request Type" = Backup, then you have some time Goal and some calendar defined. If the customer request type isn't backup then I assume you are not tracking the SLA for those or you are setting up unique SLAs for them.

[j]

Hello Jack, 

here the JQL

"Customer Request Type"="Backup"

[Jack Brickey]

Jaguliar, could you post the details on your JQL?