March 12, 2015 at 11:59 am
I have a stored procedure that uses a SQL Server database and a linked server to an Oracle database. It works when I look at the previous day, but fails when I add begin and end date parameters to choose which dates to run. I just don't see what I'm missing. The dates I'm comparing are only in the SQL database. Thanks very much for any ideas.
This works:
BEGIN
SET NOCOUNT ON;
select p.patientid, p.testid, CAST(STUFF(STUFF(mn."Arrival_Time_9", 9, 0, ' '), 12, 0, ':') AS datetime) as Arrival_date,
CAST(STUFF(STUFF(mt."Comp_D_T_22", 9, 0, ' '), 12, 0, ':') AS datetime) as Order_date, td.confirmdatetime_dt
from SQLdb.dbo.tstpatientdemographics as p inner join SQLdb.dbo.tsttestdemographics as td on p.testid = td.testid
left outer join ORACLEDB..WELSR."M01Tab01_Main" mn on td.visitnumber = mn."Pt_Acct_5"
inner JOIN ORACLEDB..WLUSR."M01Tab06_Ord" mt ON mn."Master_Recid_1" = mt."Master_Recid_1"
where td.location in (2, 4) and mt.order_4 like 'EKG%'
and datediff(DAY, td.confirmdatetime_dt, dateadd(DAY, -1, getdate())) = 0
END
But this fails:
@Begindate Datetime,
@Enddate Datetime
AS
BEGIN
SET NOCOUNT ON;
select p.patientid, p.testid, CAST(STUFF(STUFF(mn."Arrival_Time_9", 9, 0, ' '), 12, 0, ':') AS datetime) as Arrival_date,
CAST(STUFF(STUFF(mt."Comp_D_T_22", 9, 0, ' '), 12, 0, ':') AS datetime) as Order_date, td.confirmdatetime_dt
from SQLdb.dbo.tstpatientdemographics as p inner join SQLdb.dbo.tsttestdemographics as td on p.testid = td.testid
left outer join ORACLEDB..WELSR."M01Tab01_Main" mn on td.visitnumber = mn."Pt_Acct_5"
inner JOIN ORACLEDB..WLUSR."M01Tab06_Ord" mt ON mn."Master_Recid_1" = mt."Master_Recid_1"
where td.location in (2, 4) and mt.order_4 like 'EKG%'
and (td.confirmdatetime_dt >= @Begindate and td.confirmdatetime_dt < @Enddate)
END
Error message is:
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEDB" returned message "ORA-01403: no data found".
Msg 7346, Level 16, State 2, Procedure muse_ekg_er, Line 25
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLEDB".
July 13, 2015 at 9:24 am
As the parameters now allow the selection of data going back more than 1 day (SQL Server side), is it possible that there might not be any matching data on Oracle for data older than 1 day on SQL ? If getting no records back is possible, you might want to run the Oracle tables into a temp table first unless the data volume is huge...
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
July 15, 2015 at 6:49 am
Thanks for the reply. There was data for the dates. What I ended up doing is similar to using a temp table in a way. I didn't use the linked server to the Oracle database. We started a daily pull from the Oracle database into a table in a SQL Server instance and I can query against that with no date issues. So I didn't exactly solve the problem, but found a way around it.
Again thanks for all the help with this.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy