SQL server throws 'fatal error' while running Achilles

[papaul]

Hi,

Achilles runs fine for about 53% and then it errors out with the message 'sql server closed connection'. On further investigation, I found that the following statement is causing the error. Has anyone encountered this error and is there a way to fix this?

--{104 IN (@list_of_analysis_ids)}?{
-- 104 Distribution of age at first observation period by gender
insert into @results_database_schema.ACHILLES_results_dist (analysis_id, stratum_1, count_value, min_value, max_value, avg_value, stdev_value, median_value, p10_value, p25_value, p75_value, p90_value)

select 104 as analysis_id,
gender_concept_id,
COUNT_BIG(count_value) as count_value,
min(count_value) as min_value,
max(count_value) as max_value,
avg(1.0*count_value) as avg_value,
stdev(count_value) as stdev_value,
max(case when p1<=0.50 then count_value else -9999 end) as median_value,
max(case when p1<=0.10 then count_value else -9999 end) as p10_value,
max(case when p1<=0.25 then count_value else -9999 end) as p25_value,
max(case when p1<=0.75 then count_value else -9999 end) as p75_value,
max(case when p1<=0.90 then count_value else -9999 end) as p90_value

from
(
select p1.gender_concept_id,
year(op1.index_date) - p1.YEAR_OF_BIRTH as count_value,
1.0_(row_number() over (partition by p1.gender_concept_id order by year(op1.index_date) - p1.YEAR_OF_BIRTH))/(COUNT_BIG(_) over (partition by p1.gender_concept_id)+1) as p1
from
OMOP_CDM4.dbo.PERSON p1
inner join (select person_id, MIN(observation_period_start_date) as index_date from OMOP_CDM4.dbo.OBSERVATION_PERIOD group by PERSON_ID) op1 on p1.PERSON_ID = op1.PERSON_ID) t1
group by gender_concept_id

Thanks!
Paulina.

[chrisknoll]

Can you execute the above query directly on the server, and see what error message you are really getting? SQL server closed connection is probably the result of an underlying error. Just need to understand what the error of origin is.

[papaul]

Thanks Chris! The issue is fixed now when I installed the latest service pack for SQL server.

Paulina.

From: Chris Knoll <notifications@github.commailto:notifications@github.com>
Reply-To: OHDSI/Achilles <reply@reply.github.commailto:reply@reply.github.com>
Date: Thursday, July 16, 2015 at 8:52 PM
To: OHDSI/Achilles <Achilles@noreply.github.commailto:Achilles@noreply.github.com>
Cc: "Paul, Paulina" <paulina@ucsd.edumailto:paulina@ucsd.edu>
Subject: Re: [Achilles] SQL server throws 'fatal error' while running Achilles (#61)

Can you execute the above query directly on the server, and see what error message you are really getting? SQL server closed connection is probably the result of an underlying error. Just need to understand what the error of origin is.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/61#issuecomment-122161891.