Latin1_General_CI_AI - error queries with lower (ntext)

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

 

12 answers

0 vote
Jack Brickey Community Champion Oct 03, 2016

Jaguliar, could you post the details on your JQL?

Hello Jack, 

here the JQL

"Customer Request Type"="Backup"

0 vote
Jack Brickey Community Champion Oct 03, 2016

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.

Sin título.png

0 vote
Jack Brickey Community Champion Oct 04, 2016

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?

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

Sin título1.png

0 vote
Jack Brickey Community Champion Oct 05, 2016

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.

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

 

 

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

i paste the image of the same register in each case

0 vote
Jack Brickey Community Champion Oct 05, 2016

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

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,338 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot