how to pass date parameter in openquery statement

Question

I have one procedure

Create procedure [dbo].[daily_stats]
@date datetime, @grouping varchar(150)
as

select callid,campaignid,dnis,anidigits,callstarttime,totalcalltime,agenttalktime,agentid,abandon,queuewaittime,calltype,call_acw,datestamp,bill_flag,
queueid,queuename,calldelayed, callholdtime, shortcall,ctr_name,role_name,team_name,callhold_times,agent_fullname,xferto,timeafterxfer,
obcallids,obcalls,obtime,orgname, getdate() as ImportedDate

into #tempid7

from openquery(orapr2, 'select * from sp_calldetail
where trunc(callstarttime) >= trunc(sysdate-1) and abandon in (0,1) and (upper(campaignid) in (''SYNAPSE'', ''SYNAPSECALLBACK'') or campaignid like ''ME%'')')

I want to pass @date value at trunc(sysdate-1). How do I do that?

Bogdan Sahlean · Answer 1 · 2014-05-31T12:14:11.657

0

how to pass date parameter in openquery statement

Instead of using openquery function you could try this solution

EXEC LINKEDSVR_BUH_03.TestUPSERT.dbo.Customer_Select @pCustomerID = 2
EXEC LINKEDSVR_BUH_03..dbo.Customer_Select @pCustomerID = 2 -- It uses default database (see Catalog property for linked server)

or this

EXEC (N'TestUPSERT.dbo.Customer_Select ?', 2) AT LINKEDSVR_BUH_03
EXEC (N'dbo.Customer_Select ?', 2) AT LINKEDSVR_BUH_03 -- It uses default database (see Catalog property for linked server)
                            ^----- parameter placeholder
                                ^----- parameter value

edited May 31 '14 at 12:14

answered May 31 '14 at 11:28

Bogdan Sahlean

19,233
3
42
57

thanq for your response Bogdan, but my procedure is in sqlserver, and orapr2 is located on oracle server. so how do i read sql server parameter in oracle. – user3456974 May 31 '14 at 12:05
"[...]how do i read sql server parameter in oracle[...]". Please rephrase. – Bogdan Sahlean May 31 '14 at 12:15
i have to pass @date parameter for trunc(sysdate-1) date parameter is in sqlserver database, and trunc(sysdate-1) is in oracle server. – user3456974 May 31 '14 at 15:28
help me for that, i really struggle by this since yesterday. – user3456974 May 31 '14 at 15:32
"[...]for trunc(sysdate-1) date parameter[...]" TRUNC and SYSDATE are functions not parameters. – Bogdan Sahlean May 31 '14 at 19:43

score 0 · Answer 2 · answered Oct 31 '18 at 14:32

0

I found I had to convert the date-time to Oracle format - for example :

TO_DATE(''''' + CONVERT(varchar(10), @From, 120) + ''''',''''YYYY-MM-DD'''')

Otherwise it returned the error - Invalid Month

answered Oct 31 '18 at 14:32

Benno

2,534
1
17
27

how to pass date parameter in openquery statement

2 Answers2