Statement(s) could not be prepared.State: 42000

system · April 2, 2009, 3:08pm

Hi,

We have just migrated from 6x to XI3.

After migration, when I parsing the query in full client it’s throwing the below error. Connection is responding, I’m able to parse the query in 6.5.

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.State: 42000

Please advise.

Thanks
bog

bog (BOB member since 2007-08-17)

system · May 7, 2009, 12:21am

I am working on a POC in 3.1 and am getting the same error on some SQL that I can cut out of BO and run in SQL Server Management Studio .

The SQL is big an ugly so I need all of the help I can get - I also had to manually create the GROUP BY - both are problems!!:
SELECT
count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
dbo.FactClinical.PatientID
end
)
,
count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID!=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
dbo.FactClinical.PatientID
end
)
,
(count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
dbo.FactClinical.PatientID
end
)
)+(count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID!=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
dbo.FactClinical.PatientID
end
)
),
count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalSessionDate.DateTime + ClinicalSessionTime.DateTime)
end
)
,
count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID!=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalSessionDate.DateTime + ClinicalSessionTime.DateTime)
end
)
,
((count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
dbo.FactClinical.PatientID
end
)
)+(count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID!=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
dbo.FactClinical.PatientID
end
)
))+(count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID!=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalSessionDate.DateTime + ClinicalSessionTime.DateTime)
end
)
),
count
(distinct case
WHEN
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalEventTime.DateTime + ClinicalEventDate.DateTime)
end
)
,
(count
(distinct case
WHEN
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalEventTime.DateTime + ClinicalEventDate.DateTime)
end
)
)/((count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
dbo.FactClinical.PatientID
end
)
)+(count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID!=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
dbo.FactClinical.PatientID
end
)
))+(count
(distinct case
WHEN
SessionHealthCarePro.MediCompassPersonID!=‘Session Health Care Pro Unknown’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalSessionDate.DateTime + ClinicalSessionTime.DateTime)
end
)
),
count
(distinct case
WHEN
dbo.DimClinicalMeasure.ClinicalDataType=‘Blood Glucose’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalSessionDate.DateTime + ClinicalSessionTime.DateTime)
end
)
,
count
(distinct case
WHEN
dbo.DimClinicalMeasure.ClinicalDataType=‘Blood Glucose’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalEventDate.DateTime + ClinicalEventTime.DateTime)
end
)
,
count
(distinct case
WHEN
dbo.DimClinicalMeasure.ClinicalDataType=‘Blood Pressure’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalSessionDate.DateTime + ClinicalSessionTime.DateTime)
end
)
,
count
(distinct case
WHEN
dbo.DimClinicalMeasure.ClinicalDataType=‘Blood Pressure’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalEventDate.DateTime + ClinicalEventTime.DateTime)
end
)
,
count
(distinct case
WHEN
dbo.DimClinicalMeasure.ClinicalDataType=‘Walk’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalSessionDate.DateTime + ClinicalSessionTime.DateTime)
end
)
,
count
(distinct case
WHEN
dbo.DimClinicalMeasure.ClinicalDataType=‘Walk’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalEventDate.DateTime + ClinicalEventTime.DateTime)
end
)
,
count
(distinct case
WHEN
dbo.DimClinicalMeasure.ClinicalDataType=‘Weight’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalSessionDate.DateTime + ClinicalSessionTime.DateTime)
end
)
,
count
(distinct case
WHEN
dbo.DimClinicalMeasure.ClinicalDataType=‘Weight’ and
dbo.FactClinical.SessionType = ‘MCC’
then
(ClinicalEventDate.DateTime + ClinicalEventTime.DateTime)
end
)
,
ClinicalSessionDate.DateTime
FROM
dbo.FactClinical,
dbo.DimDate ClinicalSessionDate,
dbo.DimDate ClinicalEventDate,
dbo.DimTime ClinicalSessionTime,
dbo.DimTime ClinicalEventTime,
dbo.DimHealthCarePro SessionHealthCarePro,
dbo.DimClinicalMeasure
WHERE
( dbo.FactClinical.ClinicalMeasureID=dbo.DimClinicalMeasure.ClinicalMeasureID )
AND ( dbo.FactClinical.SessionTimeID=ClinicalSessionTime.TimeID )
AND ( ClinicalEventTime.TimeID=dbo.FactClinical.EventTimeID )
AND ( dbo.FactClinical.EventDateID=ClinicalEventDate.DateID )
AND ( dbo.FactClinical.SessionDateID=ClinicalSessionDate.DateID )
AND ( dbo.FactClinical.SessionHCPID=SessionHealthCarePro.HealthCareProID )
GROUP BY
ClinicalSessionDate.DateTime

Charles Killam (BOB member since 2003-04-24)

system · May 8, 2009, 10:59am

Hi There,

Please remove Order by class if you used in your query.

Thanks
mohan

mohanrajb (BOB member since 2006-09-13)

system · May 8, 2009, 2:04pm

There is no ORDER BY - without the GROUP BY, it will not even run in the SQL Server tool. I even tried to make it a derived table and that failed as well.

Charles Killam (BOB member since 2003-04-24)

system · January 12, 2010, 4:55pm

I’m getting it too, and mine is a straight select, pulling from a class that BO generated in Universe Designer from a table in a SQL DB.

Connection or SQL sentence error: (DA0005)

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.State: 42000

JB_BOlead (BOB member since 2008-04-17)

system · January 12, 2010, 8:16pm

I don’t know if it will help anyone else, but some of my fields had been altered or removed from the source DB. Once I removed them from the universe everything works fine.

The error message certainly didn’t seem to indicate missing source fields as the issue, but it was in fact generating SQL which was viewable in the data provider - so that was obviously not the issue either, so I checked out the source DB and foud that some fields had been removed from the table structure this morning.

JB_BOlead (BOB member since 2008-04-17)

system · June 24, 2010, 3:27pm

I’ve just encountered this error when migrating to XiR2 to Xi3.

Turns out that Designer was changing the value of outer joins to say ‘YES’ instead of ‘*’ (an asterisk), so all our outer joins had ‘YES’ at the beginning of them! (no wonder SQL could not parse)

To solve the issue I edited C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc\sqlsrv.prm and changed the 2 parameters below to correctly cope with outer joins:

	<Parameter Name="LEFT_OUTER">$*</Parameter>
	<Parameter Name="RIGHT_OUTER">*$</Parameter>

den7890 (BOB member since 2010-04-07)

system · June 13, 2011, 10:53pm

I know that this is a bit of an older thread, but I thought I’d add my 2 cents:

To remove any unwanted entries or additions to the GROUPBY in the SQL do the following:

In the sqlsrv.prm add the following two lines in the section of the file.

NO
Y</Parameter

Save the file and restart the Webi/Deski servers.

KevBO (BOB member since 2007-07-18)