|
|
You're in the
sql server connect
group:Calling an Oracle Stored Procedure from a Sql Server 2000 Stored Procedure using a Linked Server
sql server connect:
I'm trying to call an Oracle stored procedure from Sql server and I have this error message Could not execute procedure 'INTEGRE_INTERNET' on remote server 'ORA_DEV1'. [OLE/DB provider returned message: One or more errors occurred during processing of command.] [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.] My linked server seems to be ok, I can run a "Select " query from the query analyser to a Oracle table but I can't run a oracle stored procedure... Do you have any idea ? thanks richard
How are you executing this? What syntax are you using?
You need to be using Openquery or Openrowset and the stored procedure needs to return a result set. Umachandar Jayachandran has posted some great examples before - try doing a google search on his name and oracle stored procedure and you can find several examples. -Sue On Thu, 17 Aug 2006 15:03:18 +0200, "Richard Urrutia" [quoted text, click to view] <richard.urrutia@ciel.com> wrote:
>Hi, >I'm trying to call an Oracle stored procedure from Sql server and I have >this error message > >Could not execute procedure 'INTEGRE_INTERNET' on remote server 'ORA_DEV1'. >[OLE/DB provider returned message: One or more errors occurred during >processing of command.] >[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.] > >My linked server seems to be ok, I can run a "Select " query from the query >analyser to a Oracle table but I can't run a oracle stored procedure... > >Do you have any idea ? > >thanks > >richard > >
An Oracle stored procedure can certainly return a result
set. You can get the error you are getting if the stored procedure doesn't return a result set. And in that case you can't execute it from SQL Server. If you search for Umachandar Jayachandran's posts on the subject, I'm pretty sure he has posted an example of returning a result set in cases where you have none. I would guess you'd just add a an extra statement to execute and return a result set that you can ignore. -Sue On Fri, 18 Aug 2006 14:52:43 +0200, "Richard Urrutia" [quoted text, click to view] <richard.urrutia@ciel.com> wrote:
>Hello sue, > >I'm calling the Sub with "EXEC ORA_LINKED..DATABASE.STOREPROCEDURE" >I try with an OpenQuery with the syntax : >SELECT * FROM OPENQUERY(ORA_LINKED,'DATABASE.STOREPROCEDURE') >and I have this error message >" >Could not process object 'DATABASE.STOREPROCEDURE'. The OLE DB provider >'MSDAORA' indicates that the object has no columns. >OLE DB error trace [Non-interface error: OLE DB provider unable to process >object, since the object has no columnsProviderName='MSDAORA', >Query='DATABASE.STOREPROCEDURE']. >" > >The Oracle Stored Procedure doesn't return any value because an Oracle >Stored Procedure can't return a value. >A tried to do the same with an Oracle Function, but I have the same error. > > > > >"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message >news:650ae2lre2d0thf97lifcjp1h3i0g8s7ip@4ax.com... >> How are you executing this? What syntax are you using? >> You need to be using Openquery or Openrowset and the stored >> procedure needs to return a result set. Umachandar >> Jayachandran has posted some great examples before - try >> doing a google search on his name and oracle stored >> procedure and you can find several examples. >> >> -Sue >> >> On Thu, 17 Aug 2006 15:03:18 +0200, "Richard Urrutia" >> <richard.urrutia@ciel.com> wrote: >> >>>Hi, >>>I'm trying to call an Oracle stored procedure from Sql server and I have >>>this error message >>> >>>Could not execute procedure 'INTEGRE_INTERNET' on remote server >>>'ORA_DEV1'. >>>[OLE/DB provider returned message: One or more errors occurred during >>>processing of command.] >>>[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.] >>> >>>My linked server seems to be ok, I can run a "Select " query from the >>>query >>>analyser to a Oracle table but I can't run a oracle stored procedure... >>> >>>Do you have any idea ? >>> >>>thanks >>> >>>richard >>> >>> >> >
Not necessarily. In Oracle, you wouldn't specify database
(or catalog which is what the second position technically is called) - you specify the 4 part name with: LinkedServer..Schema.Object I don't know if Richard is referencing the schema where he used the term database, I would guess so. But it wouldn't matter anyway as you can't execute a stored procedure from SQL Server in Oracle using the 4 part naming. -Sue On Fri, 18 Aug 2006 11:26:44 -0400, "Glenn Adams [MVP - [quoted text, click to view] Retail Mgmt]" <glenn@nospam.tibercreek.com> wrote:
>Aren't those dots in the wrong place? > >Instead of "EXEC ORA_LINKED..DATABASE.STOREPROCEDURE" > >I would have expected "EXEC ORA_LINKED.DATABASE..STOREPROCEDURE" > >I've never tried to connect to an Oracle linked server, so that may have >nothing to do with your problem, but it looks wrong... > >Glenn > > >Richard Urrutia wrote: >> Hello sue, >> >> I'm calling the Sub with "EXEC ORA_LINKED..DATABASE.STOREPROCEDURE" >> I try with an OpenQuery with the syntax : >> SELECT * FROM OPENQUERY(ORA_LINKED,'DATABASE.STOREPROCEDURE') >> and I have this error message >> " >> Could not process object 'DATABASE.STOREPROCEDURE'. The OLE DB provider >> 'MSDAORA' indicates that the object has no columns. >> OLE DB error trace [Non-interface error: OLE DB provider unable to process >> object, since the object has no columnsProviderName='MSDAORA', >> Query='DATABASE.STOREPROCEDURE']. >> " >> >> The Oracle Stored Procedure doesn't return any value because an Oracle >> Stored Procedure can't return a value. >> A tried to do the same with an Oracle Function, but I have the same error. >> >> >> >> >> "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message >> news:650ae2lre2d0thf97lifcjp1h3i0g8s7ip@4ax.com... >>> How are you executing this? What syntax are you using? >>> You need to be using Openquery or Openrowset and the stored >>> procedure needs to return a result set. Umachandar >>> Jayachandran has posted some great examples before - try >>> doing a google search on his name and oracle stored >>> procedure and you can find several examples. >>> >>> -Sue >>> >>> On Thu, 17 Aug 2006 15:03:18 +0200, "Richard Urrutia" >>> <richard.urrutia@ciel.com> wrote: >>> >>>> Hi, >>>> I'm trying to call an Oracle stored procedure from Sql server and I have >>>> this error message >>>> >>>> Could not execute procedure 'INTEGRE_INTERNET' on remote server >>>> 'ORA_DEV1'. >>>> [OLE/DB provider returned message: One or more errors occurred during >>>> processing of command.] >>>> [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.] >>>> >>>> My linked server seems to be ok, I can run a "Select " query from the >>>> query >>>> analyser to a Oracle table but I can't run a oracle stored procedure... >>>> >>>> Do you have any idea ? >>>> >>>> thanks >>>> >>>> richard >>>> >>>> >> >>
Aren't those dots in the wrong place?
Instead of "EXEC ORA_LINKED..DATABASE.STOREPROCEDURE" I would have expected "EXEC ORA_LINKED.DATABASE..STOREPROCEDURE" I've never tried to connect to an Oracle linked server, so that may have nothing to do with your problem, but it looks wrong... Glenn [quoted text, click to view] Richard Urrutia wrote:
> Hello sue, > > I'm calling the Sub with "EXEC ORA_LINKED..DATABASE.STOREPROCEDURE" > I try with an OpenQuery with the syntax : > SELECT * FROM OPENQUERY(ORA_LINKED,'DATABASE.STOREPROCEDURE') > and I have this error message > " > Could not process object 'DATABASE.STOREPROCEDURE'. The OLE DB provider > 'MSDAORA' indicates that the object has no columns. > OLE DB error trace [Non-interface error: OLE DB provider unable to process > object, since the object has no columnsProviderName='MSDAORA', > Query='DATABASE.STOREPROCEDURE']. > " > > The Oracle Stored Procedure doesn't return any value because an Oracle > Stored Procedure can't return a value. > A tried to do the same with an Oracle Function, but I have the same error. > > > > > "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message > news:650ae2lre2d0thf97lifcjp1h3i0g8s7ip@4ax.com... >> How are you executing this? What syntax are you using? >> You need to be using Openquery or Openrowset and the stored >> procedure needs to return a result set. Umachandar >> Jayachandran has posted some great examples before - try >> doing a google search on his name and oracle stored >> procedure and you can find several examples. >> >> -Sue >> >> On Thu, 17 Aug 2006 15:03:18 +0200, "Richard Urrutia" >> <richard.urrutia@ciel.com> wrote: >> >>> Hi, >>> I'm trying to call an Oracle stored procedure from Sql server and I have >>> this error message >>> >>> Could not execute procedure 'INTEGRE_INTERNET' on remote server >>> 'ORA_DEV1'. >>> [OLE/DB provider returned message: One or more errors occurred during >>> processing of command.] >>> [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.] >>> >>> My linked server seems to be ok, I can run a "Select " query from the >>> query >>> analyser to a Oracle table but I can't run a oracle stored procedure... >>> >>> Do you have any idea ? >>> >>> thanks >>> >>> richard >>> >>> >
Hello sue,
I'm calling the Sub with "EXEC ORA_LINKED..DATABASE.STOREPROCEDURE" I try with an OpenQuery with the syntax : SELECT * FROM OPENQUERY(ORA_LINKED,'DATABASE.STOREPROCEDURE') and I have this error message " Could not process object 'DATABASE.STOREPROCEDURE'. The OLE DB provider 'MSDAORA' indicates that the object has no columns. OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='MSDAORA', Query='DATABASE.STOREPROCEDURE']. " The Oracle Stored Procedure doesn't return any value because an Oracle Stored Procedure can't return a value. A tried to do the same with an Oracle Function, but I have the same error. [quoted text, click to view] "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message news:650ae2lre2d0thf97lifcjp1h3i0g8s7ip@4ax.com... > How are you executing this? What syntax are you using? > You need to be using Openquery or Openrowset and the stored > procedure needs to return a result set. Umachandar > Jayachandran has posted some great examples before - try > doing a google search on his name and oracle stored > procedure and you can find several examples. > > -Sue > > On Thu, 17 Aug 2006 15:03:18 +0200, "Richard Urrutia" > <richard.urrutia@ciel.com> wrote: > >>Hi, >>I'm trying to call an Oracle stored procedure from Sql server and I have >>this error message >> >>Could not execute procedure 'INTEGRE_INTERNET' on remote server >>'ORA_DEV1'. >>[OLE/DB provider returned message: One or more errors occurred during >>processing of command.] >>[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.] >> >>My linked server seems to be ok, I can run a "Select " query from the >>query >>analyser to a Oracle table but I can't run a oracle stored procedure... >> >>Do you have any idea ? >> >>thanks >> >>richard >> >> >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |