sql server stored procedure using link to oracle fails with date parameters
-
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".
-
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) -
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