Infopath Query Test SQL Statement Fails

JimGr — Tue, 30 Oct 2012 19:10:48 GMT

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

Data Connections

Infopath Query Test SQL Statement Fails