Data Connectivity: Problems with SQL Native Client

Problems with SQL Native Client

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10723
Printed Date: 08 May 2024 at 12:22am

Topic: Problems with SQL Native Client

Posted By: Craigmeister
Subject: Problems with SQL Native Client
Date Posted: 03 Aug 2010 at 7:02am

I have recently made some changes to a few tables in my MS SQL 2005 database and a particular Crystal Reports XI report with subreports is giving me trouble. The error message when I try to preview the report is:

Failed to retreive data from the database. Details:HY000:[Microsoft][SQL Native Client] Connection is busy with results for another command.

(I can preview the subreports individually with no problem).

I am using an ODBC datasource to specify the server/database using the SQL Native Client. When I use just a plain SQL Server ODBC datasource, It works with no errors. I understand that using the plain SQL Server ODBC datasource, obscures some of the functionality improvements that is available with SQL 2005, but not with SQL 2000.

Is that what is going on here? Since I modified my tables, could it be the field types added are not supported by Crystal XI or there is something I need to do to my datasource or something else in my report to allow it to work with a SQL 2005 datatype?

Replies:

Posted By: Craigmeister
Date Posted: 03 Aug 2010 at 7:05am

Here is the SQL query for the main form.

SELECT "person"."first_name", "person"."last_name", "person"."date_of_birth", "patient"."med_rec_nbr", "patient"."person_id"
FROM "NGProd"."dbo"."patient" "patient" INNER JOIN "NGProd"."dbo"."person" "person" ON ("patient"."practice_id"="person"."practice_id") AND ("patient"."person_id"="person"."person_id")

select top 1 md_info_.ccmd1_name ,md_info_.ccmd2_name , md_info_.ccmd3_name , md_info_.ccmd4_name , md_info_.ccmd5_name , md_info_.ccmd6_name , md_info_.ccmd7_name , md_info_.ccmd8_name , md_info_.person_id from patient
inner join md_info_ on
patient.person_id = md_info_.person_id
inner join patient_encounter on
md_info_.enc_id = patient_encounter.enc_id
where md_info_.ccmd1_name != 'NULL' and md_info_.person_id = (select patient_idcode from nursechartsum_)
order by enc_timestamp desc

select top 1 md_info_.referring_md, md_info_.person_id from patient
inner join md_info_ on
patient.person_id = md_info_.person_id
inner join patient_encounter on
md_info_.enc_id = patient_encounter.enc_id
where md_info_.referring_md != 'NULL' and md_info_.person_id = (select patient_idcode from nursechartsum_)
order by enc_timestamp desc

select top 1 md_info_.pcp_name, md_info_.person_id from patient
inner join md_info_ on
patient.person_id = md_info_.person_id
inner join patient_encounter on
md_info_.enc_id = patient_encounter.enc_id
where md_info_.pcp_name != 'NULL' and md_info_.person_id = (select patient_idcode from nursechartsum_)
order by enc_timestamp desc

Posted By: Craigmeister
Date Posted: 03 Aug 2010 at 7:11am

AS far as the changes to the tables, there are several new fields added to the person, patient, and patient_encounter tables. Other than new fields of type uniqueidentifier and char and varchar, there are also new fields of type SMALLINT. None of the new fields are returned in the query, but I am guessing with the joins, the resultant dataset, however filtered, may be causing problems.

If anyone has any thoughts, I would very much appreciate it.

Posted By: hilfy
Date Posted: 04 Aug 2010 at 6:18am

After you added the fields, did you "Verify Database" in the report? One of my on-going frustrations with Crystal (since version 4.5) has been that when you add fields or indexes to a table, sometimes reports stop working - even if the fields or the index are not used in the report.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Craigmeister
Date Posted: 05 Aug 2010 at 6:51am

I get the message above after clicking Database...Verify Database.