Notifications
Clear all
Topic starter
I am working on a report for our DME department.
Query works fine in SQL Management Studio, but when I try to run it as part of a crystal report in CPS I get and error of: Unknown error 0x800A0E78
According to Microsoft this is an ADODB connection error.
If I use a variable instead of the temp table it works.
Any suggestions?
Code is attached.
DECLARE @COUNT int
DECLARE @PatID varchar(25)
DECLARE @Last varchar(25)
DECLARE @DateOfBirth varchar(25)
DECLARE @VisitID varchar(50)
SELECT @PatID=PID, @Last=LAST, @DateOfBirth = convert(varchar,convert(date, Birthdate, 103),101)
FROM PatientProfile
WHERE
(
(?PatientID.TEXT? IS NOT NULL AND PatientProfile.PatientId IN (?PatientID.TEXT?)) OR
(?PatientID.TEXT? IS NULL)
)
SET @COUNT = (SELECT count(PID) from DOCUMENT WHERE PID = @PatID AND DOCTYPE in ('1481102970001340', '1482768275001100'))
select PatientVisitID INTO #TEMPTABLE
from PatientVisit
WHERE Description like '%'+@DateOfBirth+'%'
AND Description like '%'+@Last+'%'
AND PatientProfileId in ('204985','204986','214381','214383')
AND BillStatus in (11,13,14)
IF @COUNT > 0
(SELECT PatientVisit.TicketNumber, PatientVisit.Description, PatientVisitAgg.PatBalance,convert(varchar,convert(date, PatientVisit.Visit, 103),101) as Visit
FROM PatientVisitAgg PatientVisitAgg INNER JOIN PatientVisit PatientVisit ON PatientVisitAgg.PatientVisitId=PatientVisit.PatientVisitId
WHERE PatientVisit.PatientVisitId in (Select * from #TEMPTABLE))
ELSE
SELECT PatBalance = '0'
Posted : June 10, 2016 6:20 am
Put .ml in front of the table name and see if that works.
Posted : June 10, 2016 6:38 am
Topic starter
I just rearranged the SQL and removed the temp table. Seems to have cured it.
Will try the .ml. Have another one I have to create next week that I will have to use temp tables for.
Posted : June 10, 2016 6:49 am
