Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem
|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery

Another Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

Scheduled Packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery

Anonymous Subscriptions using SQLDMO

Hi guys - I have been able to successfully create pull merge
subscriptions in SQLDMO. Now I am trying to do anonymous subscriptions and I
am having a little difficulty.
I made sure to allow anonymous subscriptions at the publication. I
created an object of type MergePullSubscription2 and called it "sub" and set
the properties as follows:
sub.SubscriptionType = .SQLDMOSubscription_Anonymous;
sub.SubscriberType = .SQLDMOMergeSubscriber_Anonymous;
(and set all the other properties appropriately) and then added it to
the MergePullSubscriptions collection.
All of that works fine. But then I try to enable it at the publisher by
using the method EnableMergeSubscription (I make sure to use the constant
SQLDMOMergeSubscriber_Anonymous for the SubscriberType argument) and that
gives me the following error:
"[Microsoft][ODBC SQL Server Driver][SQL Server]The remote server is not
defined as a subscription server."
(this is strange because I can create other subscriptions that are not
anonymous)
Is this step (EnableMergeSubscription ) necessary? It seems to be
because if I try to sync, it fails.
It seems like anonymous subs should be pretty straightforward, but I
must be missing something fundamental.
Any help will be much appreciated.
Maer
Hello,
I suggest that you refer to the information on the following web site:
MergePullSubscription Object
http://msdn.microsoft.com/library/de...us/sqldmo/dmor
ef_ob_m_0egk.asp
To create a merge anonymous subscription at the Subscriber:
1.Create a new MergePullSubscription object.
2.Set the Publisher property to the name of an existing Publisher.
3.Set the PublicationDB property to the name of the database (at the
Publisher) where the publication is located.
4.Set the Publication property to the name of the publication to which to
subscribe.
5.Set the SubscriberType property to SQLDMOMergeSubscriber_Anonymous.
6.Set the SecurityMode property of the DistributorSecurity object property
as appropriate.
7.If the SecurityMode property of the DistributorSecurity object property
is set to SQLDMOReplSecurity_Normal, set the StandardLogin and
StandardPassword properties of the DistributorSecurity object property.
8.Set the SecurityMode property of the PublisherSecurity object property
as appropriate.
9.If the SecurityMode property of the PublisherSecurity object property is
set to SQLDMOReplSecurity_Normal, set the StandardLogin and
StandardPassword properties of the PublisherSecurity object property.
10.Note that the Name property defaults to
publisher:publication_database:publication.
11.Add the MergePullSubscription object to the MergePullSubscriptions
collection of a connected ReplicationDatabase object at the Subscriber.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Anonymous Subscription not showing as an option

I am attempting to create an anonymous subscription on a new install of SQL
Server 2000 (new server). I have successfully done this other
instances/servers. Why does the publication, which allows anonymous
subscritions, not show in my options?
I can ping the server just fine. I am sure I am just overlooking something.
Thanks-Deb
Is the account your SQL Server agent is running under on the subscriber in
the PAL on the Publisher?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Deb" <Deb@.discussions.microsoft.com> wrote in message
news:EC90B821-98B5-47F8-83B7-39C412EDC86D@.microsoft.com...
>I am attempting to create an anonymous subscription on a new install of SQL
> Server 2000 (new server). I have successfully done this other
> instances/servers. Why does the publication, which allows anonymous
> subscritions, not show in my options?
> I can ping the server just fine. I am sure I am just overlooking
> something.
> --
> Thanks-Deb
|||Yes... it is. I also listed this new server as a subscriber under Publisher
and Distributor properties.
Deb
"Deb" wrote:

> I am attempting to create an anonymous subscription on a new install of SQL
> Server 2000 (new server). I have successfully done this other
> instances/servers. Why does the publication, which allows anonymous
> subscritions, not show in my options?
> I can ping the server just fine. I am sure I am just overlooking something.
> --
> Thanks-Deb
|||Yes...it is. I also listed the server under Subscribers in the Publisher and
Distributor properties tab.
Deb
"Hilary Cotter" wrote:

> Is the account your SQL Server agent is running under on the subscriber in
> the PAL on the Publisher?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Deb" <Deb@.discussions.microsoft.com> wrote in message
> news:EC90B821-98B5-47F8-83B7-39C412EDC86D@.microsoft.com...
>
>
|||Is your subscriber by chance an MSDE subscriber?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Deb" <Deb@.discussions.microsoft.com> wrote in message
news:9D6E7DDD-6911-4E6A-BF9B-93001BEDADDC@.microsoft.com...[vbcol=seagreen]
> Yes...it is. I also listed the server under Subscribers in the Publisher
> and
> Distributor properties tab.
> --
> Deb
>
> "Hilary Cotter" wrote:
|||It should be able to see it then. Could you also try this - add the account
to the dbo group in the distribution database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Deb" <Deb@.discussions.microsoft.com> wrote in message
news:2EB34604-83BE-4106-ABB9-3A88C2D2D941@.microsoft.com...[vbcol=seagreen]
> Yes... it is. I also listed this new server as a subscriber under
> Publisher
> and Distributor properties.
> --
> Deb
>
> "Deb" wrote:
|||I thought of that one as well. I added it just trying to cover my bases. No
go.
What I find interesting is I can see other Anonymous Snapshots available on
other servers and this user is not a part PAL or Sys Amin role on these
servers whatsoever.
Deb
"Hilary Cotter" wrote:

> It should be able to see it then. Could you also try this - add the account
> to the dbo group in the distribution database.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Deb" <Deb@.discussions.microsoft.com> wrote in message
> news:2EB34604-83BE-4106-ABB9-3A88C2D2D941@.microsoft.com...
>
>

Analysis Services Access Database

Hello,

I am trying to generate a cube in SSAS 2005 with an Access database as the data source. I can create the Data Source and successfully test the connection. Here is my connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DB\LSAM.mdb

I can also successfully create my Data Source View and I can return data by right clicking on any of my table objects and selecting 'Explore Data'

However, when I try to generate my cube, I receive the following error message:

OLE DB error: OLE DB or ODBC error: 'E:\DB\LSAM.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.; 3044.

Any assistance would be GREATLY appreciated.

Thank You,

Joshua Shull.

Try and import your Access database into SQL Server and build your cube from there.

Later you can try and change datasource to point to the access file and process it .

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.