September 12, 2012 at 9:39 am
I'm receiving this error message ""ORA-00933: SQL command not properly ended' when trying to execute the following sp against Oracle link server
DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT * FROM OPENQUERY(OraSvr,''SELECT
a.stop
, a.tempid
, a.tempnbr
, a.temphome
, b.tempacct
, b.tempaddress
, b.tempdl
, b.tempcust
, a.tempname
FROM cms.ech AS a
WHERE left outer join cti.customercallhistory b on a.tempnbr
= b.tempacct
and a.stop > trunc(sysdate)
and a.tempcode = 1
and a.tempcoderef = 0
and ROWNUM <= 100 '')';
Exec (@sql)
Tried all I know, can't figure it out.. Please help, working under a deadline..
September 12, 2012 at 9:46 am
fatish.scott (9/12/2012)
I'm receiving this error message ""ORA-00933: SQL command not properly ended' when trying to execute the following sp against Oracle link serverDECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT * FROM OPENQUERY(OraSvr,''SELECT
a.stop
, a.tempid
, a.tempnbr
, a.temphome
, b.tempacct
, b.tempaddress
, b.tempdl
, b.tempcust
, a.tempname
FROM cms.ech AS a
WHERE left outer join cti.customercallhistory b on a.tempnbr
= b.tempacct
and a.stop > trunc(sysdate)
and a.tempcode = 1
and a.tempcoderef = 0
and ROWNUM <= 100 '')';
Exec (@sql)
Tried all I know, can't figure it out.. Please help, working under a deadline..
Try this:
DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT * FROM OPENQUERY(OraSvr,''SELECT
a.stop
, a.tempid
, a.tempnbr
, a.temphome
, b.tempacct
, b.tempaddress
, b.tempdl
, b.tempcust
, a.tempname
FROM
cms.ech AS a
left outer join cti.customercallhistory b
on a.tempnbr = b.tempacct
WHERE
a.stop > trunc(sysdate)
and a.tempcode = 1
and a.tempcoderef = 0
and ROWNUM <= 100 '')';
Exec (@sql)
September 12, 2012 at 10:44 am
I'm still getting the same error message.
September 12, 2012 at 10:54 am
ORA-00933: SQL command not properly ended
Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order.
Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces.
Find an Oracle reference manual (sory but I haven't worked with Oracle for a year now, and only worked with it for a year), but I think the problem is the ROWNUM in the where clause. As I look at it, it just doesn't look right. I know you can get the row number of a record in Oracle, but that is my guess at this point.
September 12, 2012 at 11:16 am
Decided to do some looking. Not sure, but maybe all you need is a semicolon.
DECLARE @sql NVARCHAR(4000)
SET @sql = 'SELECT * FROM OPENQUERY(OraSvr,''SELECT
a.stop
, a.tempid
, a.tempnbr
, a.temphome
, b.tempacct
, b.tempaddress
, b.tempdl
, b.tempcust
, a.tempname
FROM
cms.ech AS a
left outer join cti.customercallhistory b
on a.tempnbr = b.tempacct
WHERE
a.stop > trunc(sysdate)
and a.tempcode = 1
and a.tempcoderef = 0
and ROWNUM <= 100; '')';
Exec (@sql)
Every sample in the Oracle reference has a semicolon at the end.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply