I'm attempting to use the query below in an Infopath Data Connection to SQL Server. When I click on Test SQL Statement button it errors with the following error mesage:
The ADO Adapter failed to get the schema.
[0x80004005][Microsoft OLE DB Provider for ODBC Drivers] [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
I am using the same ODB Connection for other queries on the same form without any problems.
Here is the QUERY. Any ideas why Infopath doesn't like the query??
Please let me know. I've wasted enough time on this already.
WITH Client_Data as
(
select c.client_id, c.client_number, r.Reference_Number, cp.Capacity, cp.capacity_description, r.referral_end_date, c.dob, c.Client_Name, c.Surname,c.Firstname
,(select top 1 ba.Address_Summary
from dbo.AdHoc_Base_Addresses ba
join dbo.AdHoc_Client_Contacts cc on ba.Client_Contact_Id = cc.Client_Contact_Id
where c.client_id = cc.client_id
and (cc.end_date is null or cc.end_date >= r.referral_end_date)
and (ba.end_date is null or ba.end_date >= r.referral_end_date)
and cc.Client_Contact_Relationship_Summary like '%legal%'
order by (coalesce(cc.end_date, getdate()))
) as cc_Address
,(select top 1 cc.Client_Contact_Name
from dbo.AdHoc_Base_Addresses ba
join dbo.AdHoc_Client_Contacts cc on ba.Client_Contact_Id = cc.Client_Contact_Id
where c.client_id = cc.client_id
and (cc.end_date is null or cc.end_date >= r.referral_end_date)
and (ba.end_date is null or ba.end_date >= r.referral_end_date)
and cc.Client_Contact_Relationship_Summary like '%legal%'
order by (coalesce(cc.end_date, getdate()))
) as Client_Contact_Name
,(select top 1 ca.Present_Location_Address
from dbo.AdHoc_Client_Addresses ca
WHERE
ca.Client_Id = c.Client_Id
) as cl_address
from adhoc_clients c
join AdHoc_Client_Profile cp on c.client_id = cp.client_id
join adhoc_referrals r on c.client_id = r.client_id
where r.referral_service_type in ('Long Term Placement', 'Short Stay - Interim', 'Short Stay Convalescent Care',
'Short Stay Respite')
and (r.Referral_End_Date is null or r.Referral_End_Date >= GETDATE() -30)
)
SELECT
Client_Data.client_id,
Client_Data.client_number,
Client_Data.Reference_Number,
Client_Data.Capacity,
Client_Data.capacity_description,
Client_Data.referral_end_date,
Client_Data.dob,
Client_Data.Client_Name,
cc_Address,
Client_Contact_Name,
cl_address
,case when Client_Data.capacity = 'I' then cc_Address
else cl_address end as mailing_Address
,case when Client_Data.capacity = 'I' then (Client_Data.Firstname+' '+Client_Data.Surname + ' C/O ' + Client_Contact_Name)
else Client_Data.client_name end as name
FROM Client_Data