Home » SQL & PL/SQL » SQL & PL/SQL » Insert into openquery error
Insert into openquery error [message #268164] Mon, 17 September 2007 18:25 Go to next message
sot2007
Messages: 1
Registered: September 2007
Junior Member
Hello All,

I have an insert openquery statement that is giving the below error; does anyone know what I'm doing wrong? Please advice. Thanks.

declare @Report_Date as DATETIME

set @Report_Date = '09/13/2007'

--** DECLARE VARIABLE **--
DECLARE @sqlstring nvarchar(1000)

--** DELETE POSSIBLE DUPLICATES **--
PRINT 'DELETING DUPLICATE RECORDS'
DELETE FROM TEST_DATA
WHERE REPORT_DATE = @Report_Date

--** QUERY AND LOAD DATA **--
SET @sqlstring='INSERT INTO TEST_DATA SELECT ''' + CONVERT(VARCHAR(12), @Report_Date, 101) + ''', SAMPLE.* FROM OPENQUERY(ORC1,''SELECT ENTERED_ID,
NEXT_VALUE_DATE FROM TEST_DATA where NEXT_CALLTIME = ('''''+ CONVERT(varchar(12),@dtRptDateDetail,101) + ''''',''''MM/DD/YYYY'''')'') AS SAMPLE'

PRINT 'LOADING TEST_DATA'

EXEC sp_executesql @sqlstring


I'm getting this error which is shown below...

Error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-01797: this operator must be followed by ANY or ALL
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80004005: ].

Does anyone know what I'm doing wrong? Please advice. Thanks.
Re: Insert into openquery error [message #268165 is a reply to message #268164] Mon, 17 September 2007 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOW posting guidelines as stated in #1 STICKY post at top of this forum.

>I have an insert openquery statement
HUH???????????????????????? Please translate to English or valid SQL.

I suggest you do what it takes to capture the vanilla SQL statement that is actually being passed to Oracle & test in within SQL*Plus.

[Updated on: Mon, 17 September 2007 18:28] by Moderator

Report message to a moderator

Re: Insert into openquery error [message #268167 is a reply to message #268164] Mon, 17 September 2007 18:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
This is a reminder.
When dealing with Oracle, characters between single quote marks are STRING datatypes.
For example 'this is not a date 2007-12-31' the preceeding is a string & so is the following '2007-12-31' is also a STRING!
Re: Insert into openquery error [message #268237 is a reply to message #268164] Tue, 18 September 2007 00:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is an ORACLE forum.
What you posted is not Oracle code.

Regards
Michel
Previous Topic: Parallel Pipelined Funtion with MERGE returns ORA-00905: missing keyword
Next Topic: Cursor with parameters
Goto Forum:
  


Current Time: Sun Dec 04 20:25:00 CST 2016

Total time taken to generate the page: 0.12601 seconds